1

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',',') ;
Matt
  • 14,906
  • 27
  • 99
  • 149
napster
  • 349
  • 1
  • 4
  • 16
  • 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 Dec 11 '14 at 11:19
  • some versions of postgres seem to have json query built in: http://stackoverflow.com/questions/10560394/how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype – pherris Dec 11 '14 at 11:26
  • See this [answer](https://stackoverflow.com/questions/47239225/how-to-search-sql-column-containing-json-array) which uses OPENJSON, much better than LIKE. – paultechguy Nov 16 '17 at 15:57
  • See this [answer](https://stackoverflow.com/questions/47239225/how-to-search-sql-column-containing-json-array) which is much better than using LIKE. – paultechguy Nov 16 '17 at 15:59

1 Answers1

3

Use the % wild card and a LIKE statement

SELECT * FROM data_table WHERE data LIKE '%"bbb%"';
Matt
  • 14,906
  • 27
  • 99
  • 149
  • thanks @Matt... but i want exact match, other rows may contain something like 'bbbbb' or 'abbb'. – napster Dec 11 '14 at 11:31