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"}]

In the function, I check for the value using:

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||'}]'

which did not help.

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

Answer

You should be able to just cast:

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

Or you can use the json builder functions:

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