querying JSON field

[deelienardy]

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 ?

[kyleconroy]

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;

[deelienardy]

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 ?

[zzhaolei]

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

[domino14]

why is it bad to query JSONB?

[acaloiaro]

It's not.

[coolaj86]

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.