Paramaters matched to JSON fields are the wrong type #743

Activity

1. kyleconroy

   added

   bugSomething isn't workingSomething isn't working

   📚 postgresql

   on Oct 23, 2020
2. Abdillah-Epi commented on May 31, 2021

   

   Abdillah-Epi

   on May 31, 2021

   More actions

   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 :/

   👍React with 👍1Reacted by Edouard CLAUDE
3. kyleconroy

   added

   analyzer

   on Sep 22, 2023
4. speix commented on Sep 26, 2023

   

   speix

   on Sep 26, 2023

   Last edited by speix

   More actions

   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.
5. kyleconroy

   added a commit that references this issue on Oct 5, 2023

   Fix #743

   ecd8d04
6. kyleconroy commented on Oct 24, 2023

   

   kyleconroy

   on Oct 24, 2023

   CollaboratorAuthor

   More actions

   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
7. kyleconroy

   closed this as completedon Oct 24, 2023