CODEWITHSUNDEEP
arraysjsonpostgresql
Pankaj Monga
Pankaj Monga

Reputation: 179

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

 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 |

Upvotes: 2

Views: 1096

Answers (1)

Patrick
Patrick

Reputation: 32234

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

Upvotes: 2

Related Questions