- Notifications
You must be signed in to change notification settings - Fork976
querying JSON field#738
-
i have table something like this on postgres while i know it is bad querying from JSON, but this is legacy data structure, so shit happen let say data on metadata is like this when i create query the generated parameters will become like this So, the question should it type on parameter become |
BetaWas 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 beinterface{} instead ofjson.RawMessage. I've created#743 to track that bug.
You probably know the type of address1, so you can alwayscast it to text:
-- name: FindByAddress :oneSELECT * 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 You probably know the type of address1, so you can alwayscast it to text: |
BetaWas 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 or whatever type to cast ? |
BetaWas this translation helpful?Give feedback.
All reactions
Uh oh!
There was an error while loading.Please reload this page.
Uh oh!
There was an error while loading.Please reload this page.
-
mysql has the same problem when using |
BetaWas this translation helpful?Give feedback.
All reactions
-
why is it bad to query JSONB? |
BetaWas this translation helpful?Give feedback.
All reactions
-
It's not. |
BetaWas this translation helpful?Give feedback.
All reactions
Uh oh!
There was an error while loading.Please reload this page.
Uh oh!
There was an error while loading.Please reload this page.
-
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. |
BetaWas this translation helpful?Give feedback.
All reactions
This discussion was converted from issue #738 on October 23, 2020 16:40.