while i know it is bad querying from JSON, but this is legacy data structure, so shit happen
\nhahaha

let say data on metadata is like this

{\n    \"address1\": \"test\"\n}\n

when i create query

-- name: FindByAddress :one\nSELECT * FROM \"user\" WHERE \"metadata\"->>'address1' = $1 LIMIT 1\n

the generated parameters will become like this

type FindByAddressParams struct {\n\tmetadata json.RawMessage\n}\n

So, the question should it type on parameter become interface{} since just the owner of data who knows JSON value ?

The type of the address1 field on the JSON column isn't known at compile time. You're correct that the parameter type should be interface{} instead of json.RawMessage. I've created #743 to track that bug.

You probably know the type of address1, so you can always cast it to text:

-- name: FindByAddress :one\nSELECT * FROM authors WHERE \"metadata\"->>'address1' = $1::text LIMIT 1;\n

querying JSON field #738

Answered by kyleconroy

deelienardy asked this question in Q&A

querying JSON field #738

deelienardy

Oct 20, 2020 · 2 comments · 4 replies

Answered by kyleconroy Return to top

Discussion options

deelienardy

Oct 20, 2020

i have table something like this on postgres CREATE TABLE "user" ( "id" INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, "metadata" JSONB ) while i know it is bad querying from JSON, but this is legacy data structure, so shit happen hahaha let say data on metadata is like this { "address1": "test" } when i create query -- name: FindByAddress :one SELECT * FROM "user" WHERE "metadata"->>'address1' = $1 LIMIT 1 the generated parameters will become like this type FindByAddressParams struct { metadata json.RawMessage } So, the question should it type on parameter become interface{} since just the owner of data who knows JSON value ?

1 You must be logged in to vote

Answered by kyleconroy Oct 23, 2020

The type of the address1 field on the JSON column isn't known at compile time. You're correct that the parameter type should be interface{} instead of json.RawMessage. I've created #743 to track that bug.

You probably know the type of address1, so you can always cast it to text:

-- name: FindByAddress :one
SELECT * FROM authors WHERE "metadata"->>'address1' = $1::text LIMIT 1;

View full answer

Replies: 2 comments · 4 replies

Comment options

kyleconroy

Oct 23, 2020

Maintainer

The type of the address1 field on the JSON column isn't known at compile time. You're correct that the parameter type should be interface{} instead of json.RawMessage. I've created #743 to track that bug. You probably know the type of address1, so you can always cast it to text: -- name: FindByAddress :one SELECT * FROM authors WHERE "metadata"->>'address1' = $1::text LIMIT 1;

Marked as answer

1 You must be logged in to vote

2 replies

Comment options

deelienardy Oct 26, 2020

Author

Hi @kyleconroy, before thanks for the reply. The link to playground is broken, so assuming it, i guess what do you mean is like this SELECT * FROM "user" WHERE "metadata"->>'address1'::TEXT = $1 LIMIT 1; or whatever type to cast ?

Comment options

edited

zzhaolei Mar 15, 2025

mysql has the same problem when using json_extract(field, '$.field') = ? and field->>'$.field' = ?

Answer selected by kyleconroy

Comment options

domino14

Dec 4, 2023

why is it bad to query JSONB?

2 You must be logged in to vote

2 replies

Comment options

acaloiaro Mar 13, 2024

It's not.

Comment options

edited

coolaj86 May 2, 2025

Querying JSONB potentially triggers a full table scan. If performance has become or is likely to become an issue as rows are added to the table, an indexed column may need to be added to the query or the queried property may need to be moved or copied out to an indexed column.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Category

🙏

Q&A

Labels

None yet

6 participants

Converted from issue

This discussion was converted from issue #738 on October 23, 2020 16:40.