CODEWITHSUNDEEP
sqlsql-serverjsonsql-server-2008postgresql-9.3
napster
napster

Reputation: 369

how to match string in JSON array?

I have a JSON column called data in a table called , The table looks something like this:

select * from data_table;

 id |           data   
----+------------------------
  1 | ["a","aa","aaa","aaaa"]
  2 | ["b","bb","bbb","bbbb"]
  3 | ["c","cc","ccc","cccc"]

What I would like to do is query the table for all data_table that matches the 'bbb' value in the data column?

I have tried things like this but to no avail:

SELECT * 
FROM data_table 
WHERE data::text[] @> string_to_array('bbb',',') ;

Upvotes: 1

Views: 2919

Answers (1)

Matt
Matt

Reputation: 15061

Use the % wild card and a LIKE statement

SELECT * FROM data_table WHERE data LIKE '%"bbb%"';

Upvotes: 3

Related Questions