1

I have a JSON node on which I have to write a PSQL query, My table schema name(String),tagValues(jsonb). Example tagValue data is given below

Name_TagsTable

 uid |       name(String)|          tagValues(jsonb)
-----+-------------------+-----------------------------
   1 |     myName        |    { "tags": ["xyz","pqr","xyp"]}

I need a query that returns all rows for a search "pq" made on the tagValues of the table

select * from Name_TagsTable where tagValues->tags contains %pq%
Tarun Annapareddy
  • 33
  • 4

2 Answers2

0

You can use LIKE operator along with casting JSONB value to a string type such as

SELECT *
  FROM Name_TagsTable
 WHERE (tagValues->'tags')::TEXT LIKE '%pq%'
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

You need to unnest the elements, then you can use it in a WHERE condition that applies a LIKE condition.

select nt.*
from name_tagstable nt
where exists (select *
              from jsonb_array_elements_text(tagvalue -> 'tags') as a(tag)
              where a.tag like '%pg%');