I am trying to get the rows from a table where I want the condition to check for a value from a jsonb column. The column stores the data as: In the function, I check for the value using: but I want the 420 to be replaced with "ID" which I pass through the function. The only way I came

PostgreSQL(function) – Implementation of where clause to get rows by a value(concatenate) in the JSONB column

I am trying to get the rows from a table where I want the condition to check for a value from a jsonb column. The column stores the data as:

[{"UserId": 420, "Permission": "Create"}, {"UserId": 369, "Permission": "View"}]

​x
 
[{"UserId": 420, "Permission": "Create"}, {"UserId": 369, "Permission": "View"}]​

In the function, I check for the value using:

tab."Books" @> '[{"UserId":420}]'

 
tab."Books" @> '[{"UserId":420}]'​

but I want the 420 to be replaced with “ID” which I pass through the function. The only way I came across was concatenation(

tab."Books" @> '[{"UserId":'||ID||'}]'

 
tab."Books" @> '[{"UserId":'||ID||'}]'​

which did not help.

Am I doing it wrong? Kindly suggest an alternative if any. Thanks.

You should be able to just cast:

tab."Books" @> ('[{"UserId":' || ID || '}]')::jsonb

 
tab."Books" @> ('[{"UserId":' || ID || '}]')::jsonb​

Or you can use the json builder functions:

tab."Books" @> jsonb_build_array(jsonb_build_object('UserId', ID))

 
tab."Books" @> jsonb_build_array(jsonb_build_object('UserId', ID))​