Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Query to search over array elements inside jsonb PSQL

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

select * from Name_TagsTable where tagValues->tags contains %pq%  

>Solution :

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%' 
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading