Paramaters matched to JSON fields are the wrong type

[kyleconroy]

These parameters should end up as interface{}, not json.RawMessage.

CREATE TABLE "user" (
    "id" INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    "metadata" JSONB
)

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

type FindByAddressParams struct {
	metadata json.RawMessage
}

• Initial report

https://play.sqlc.dev/p/cd6227106fb8fb969d1fc9699dd9c03046e26578c589268e30d23573f67037bb

[Abdillah-Epi]

Hi @kyleconroy, I have the same problem as in issue #738, I have table like this:

CREATE TABLE "user" (
    "id" INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    "metadata" JSONB
)

but instead of having an object, I have an array of users:

[
    {
        "lic":"1137868E",
        "firstname":"Joe",
        "lastname":"Bag"
    },
    {
        "lic":"1295364R",
        "firstname":"HA",
        "lastname":"Dégue"
    },
]

then I wrote a query to get a user by "lic"

-- name: FindUserByLic :one
SELECT * FROM user AS u WHERE u.deleted_at IS NULL AND u.metadata @> $1; -- > $1 suppose to be like this '[{"lic":"1295364R"}]'

and I got this parameter:

type FindUserByLic struct {
	metadata json.RawMessage
}

here is my code:

	lic, err := json.Marshal(fmt.Sprintf(`'[{"lic":"%s"}]'`, req.Lic))
	if err != nil {
		return err
	}
	arg := db.FindUserByLicParams{
		Metadata: lic,
	}

	contests, err := server.store.FindUserByLic(ctx, arg) //find user by lic
	if err != nil {
		return err
	}

but in the end I had an empty array :/

[speix]

That is not a bug, I have a solution for that or any parametrized representation of jsonb operations, the Postgres way.

First you need to define the datatype of your parameter on your sqlc definitions, for example:

... and metadata @> sqlc.arg(metadata)::jsonb -- Here you expect to input: '[{"id":"1234567890"}]'

After generating the code, you would expect the input to be of type pgtype.JSONB.

Then in your go code, you would have to have an interface conversion to JSONB:

func InterfaceToJSONB(data interface{}) pgtype.JSONB {
	var jsonB pgtype.JSONB
	err := jsonB.Set(data)
	return jsonB
}

Last, running your query, you have this:

param3 := "1234567890"
someData, err := q.SomeQueryName(ctx, db.SomeQueryNameParams{
		ParamName1:  param1,
		ParamName2: param2,
		Metadata: InterfaceToJSONB(`[{"id":"` + param3 + `"}]`),
	})

And works as you would normally write your query in the console.

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground