Skip to content

Paramaters matched to JSON fields are the wrong type #743

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
kyleconroy opened this issue Oct 23, 2020 · 3 comments
Closed

Paramaters matched to JSON fields are the wrong type #743

kyleconroy opened this issue Oct 23, 2020 · 3 comments
Labels
analyzer 📚 postgresql bug Something isn't working

Comments

@kyleconroy
Copy link
Collaborator

kyleconroy commented Oct 23, 2020

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
}

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

@kyleconroy kyleconroy added bug Something isn't working 📚 postgresql labels Oct 23, 2020
@Abdillah-Epi
Copy link

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
Copy link

speix commented Sep 26, 2023

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 added a commit that referenced this issue Oct 5, 2023
@kyleconroy
Copy link
Collaborator Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
analyzer 📚 postgresql bug Something isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants