Search code examples
postgresqlpostgresql-9.6

How do I parameterize a search for jsonb array contains?

I have colA, which is a jsonb column with an array. Here are some sample rows:

["postgresql", "mysql", "elasticsearch"]
["python", "perl"]

I am trying to search for if the array has "postgresql" or "mysql":

SELECT 'colA @> ANY (ARRAY ['["postgresql"]', '["mysql"]']::jsonb[])' FROM mytable

That returns the first row. Now, since users will be passing data then I need to parameterize the queries:

SELECT 'colA @> ANY (ARRAY ['[$1]', '[$2]']::jsonb[])' FROM mytable

But I get:

ERROR: syntax error at or near "["
Solution

  • You may cross join the table with jsonb_array_elements_text() in the from clause which allows you to filter records in the where clause based on the corresponding values obtained.

    select t.id,j.* from mytable t cross join 
               jsonb_array_elements_text(cola) as j(val)
 where j.val  IN ( 'postgresql','mysql');

    DEMO