Question 115065 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Search if jsonb array of string contains value

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found something like this:

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

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search