-
Couldn't load subscription status.
- Fork 958
querying JSON field #738
-
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 ?
Beta Was this translation helpful? Give feedback.
All reactions
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;
Replies: 2 comments 4 replies
-
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;
Beta Was this translation helpful? Give feedback.
All reactions
-
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 ?
Beta Was this translation helpful? Give feedback.
All reactions
-
mysql has the same problem when using json_extract(field, '$.field') = ? and field->>'$.field' = ?
Beta Was this translation helpful? Give feedback.
All reactions
-
why is it bad to query JSONB?
Beta Was this translation helpful? Give feedback.
All reactions
-
It's not.
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.