how to use where clause to check value exist in array in postgres

Question

 actName |       applicable       | status | id | 
-----------------------------------------------------
   test1 | {"applicable":[1,3,7]} |      1 |  1 | 
   test2 | {"applicable":[5,4,1]} |      1 |  2 |

Is it possible to check value in array applicable? E.g. if I try to find which row contains integer value 3 in row column applicable then it must returns one row that is first row.

result :

 actName |       applicable       | status | id | 
-----------------------------------------------------
   test1 | {"applicable":[1,3,7]} |      1 |  1 |

I'm not very familiar with arrays in postgres, but from first sight I would try SELECT * FROM table WHERE applicable LIKE '%,3,%' . — Jörn Buitink
– Jörn Buitink, Commented Jan 13, 2016 at 7:43
PG version? Is column applicable of json or jsonb type? — Patrick
– Patrick, Commented Jan 13, 2016 at 7:45

Patrick · Accepted Answer · 2016-01-13 09:51:55Z

2

You need the function json_array_elements() to unnest the json array so that you can inspect it's elements. Then it is rather easy:

SELECT *
FROM my_table
WHERE id IN (
  SELECT id
  FROM (
    SELECT id, json_array_elements(applicable->'applicable')::text::int AS chk
    FROM my_table) sub
  WHERE chk = 3);

Since json values (and thus array elements) can be of any type they need to be cast to an integer using ::text::int. You need the sub-select to check for those id values that have the value 3 in the array. Note that this will return multiple rows having the same array value. If there can be multiple duplicate array values in a single row you should SELECT DISTINCT * to avoid returning the same row multiple times.

SQLFiddle

edited Jan 13, 2016 at 9:51

answered Jan 13, 2016 at 8:20

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pankaj Monga Over a year ago

it gives error ERROR: syntax error at or near "sub"

Patrick Over a year ago

For me it works perfectly fine, PG 9.4.5. Did you get all the parentheses right?

Collectives™ on Stack Overflow

how to use where clause to check value exist in array in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related