Postgresql json like query

postgresql jsonb
adviner picture adviner · Mar 21, 2017 · Viewed 40.9k times · Source

I have the following table called module_data. Currently it has three rows of entries:

                id                               data
0ab5203b-9157-4934-8aba-1512afb0abd0 {"title":"Board of Supervisors Meeting","id":"1i3Ytw1mw98"}
7ee33a18-63da-4432-8967-bde5a44347a0 {"title":"Board of Supervisors Meeting","id":"4-dNAg2mn6o"}
8d71ca35-74eb-4751-b635-114bf04843f1 {"title":"COPD 101", "id":"l9O0jCR-sxg"}

Column data's datatype is jsonb. I'm trying to query it using like operator. Something like the following:

SELECT * FROM module_data WHERE title LIKE '%Board%';

I've been looking at the jsonb support and there doesn't seem to be a like operator. If anyone has any advice.

Answer

Gurwinder Singh picture Gurwinder Singh · Mar 21, 2017

If the data column is text type, then use ->> on cast:

select * from module_data where data::json->>'title' like '%Board%'

If it's already json:

select * from module_data where data->>'title' like '%Board%'

Related questions

How to perform update operations on columns of type JSONB in Postgres 9.4

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. Documentation for JSONB types and functions: http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.…

Read More
Query for array elements inside JSON type

I'm trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" } I would …

Read More
Explanation of JSONB introduced by PostgreSQL

PostgreSQL just introduced JSONB and it's already trending on hacker news. It would be great if someone could explain how it's different from Hstore and JSON previously present in PostgreSQL. What are its advantages and limitations and when should someone …

Read More