Postgres: searching json array for int field value

user_1736218 0
Viewed 55

Following this documentation: https://www.postgresql.org/docs/9.5/functions-json.html I came across this syntax for searching a json array for a constant value using single quotes.

I'd like to do the same but search for the value of a field in a table I'm joining to. I've tried a number of variations of this:

SELECT tableA.id, tableB.json_array FROM tableA
LEFT JOIN tableB ON (tableB.json_array)::jsonb @> tableA.id;

But am always running into type-related issues. Does the @> operator only work with constants? How can I solve this problem?

postgresql
1 Answers

If your data is in JSON ARRAY format then you can use Postgres jsonb_array_elements_text function which is extracting values of array elements. After doing this you can easily use key values in a query or on where conditions.

Sample query for you:

-- sample format for json_array field:  [{"id": 110}, {"id": 115}, {"id": 130}, {"id": 145}, {"id": 152}, {"id": 165}]  
select b.* from tableB b
cross join jsonb_array_elements_text(b.json_array) b2(pvalue) 
where 
    (b2.pvalue::jsonb->'id')::int4 > 100 
    -- (b2.pvalue::jsonb->'id')::int4 = 102
    -- (b2.pvalue::jsonb->'id')::int4 in (50, 51, 55)
user_172960840
Related