how to match string in JSON array?

Question

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',',') ;

As in any line that contains bbb regardless of what is around it you want it to return? So in the example above it would return the second line? — Matt
– Matt, Commented Dec 11, 2014 at 11:19
some versions of postgres seem to have json query built in: stackoverflow.com/questions/10560394/… — pherris
– pherris, Commented Dec 11, 2014 at 11:26

Matt · Accepted Answer · 2014-12-11 11:33:16Z

3

Use the % wild card and a LIKE statement

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

edited Dec 11, 2014 at 11:33

answered Dec 11, 2014 at 11:20

Matt

15.1k28 gold badges109 silver badges161 bronze badges

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

1 Comment

napster Over a year ago

thanks @Matt... but i want exact match, other rows may contain something like 'bbbbb' or 'abbb'.

Collectives™ on Stack Overflow

how to match string in JSON array?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related