Search if jsonb array of string contains value-CodePudding

I have arry of strings in a column like : ["196 2616", "9503744", "36.25260-6027", "2 414 425", "7 034 771 6", "F709714", "1088229", "183144", "505870338", "105075"]

I want to search if this array contains for example 2 414 425.

I tried something like this: SELECT * FROM table_name t where t.numbers @> '2 414 425' But it doesnt return anything even when it should.

CodePudding user response：

The @> operator needs a proper JSON object on the right hand side:

where t.numbers @> '["2 414 425"]'

This should work if numbers is defined as jsonb (which it should be). Otherwise cast it numbers::jsonb

Checking if at least one of multiple keys is contained in the JSON array can be done using the ?| operator:

where t.numbers ?| array['2 414 425', '13261999']

The ?& operator will check if all those keys are contained in the JSON array.

CodePudding user response：

I found something like this:

select carat_id from table where (numbers)::jsonb ? '13261999';