Postgresql json as request

Question

I have the following table named module_data. There are currently 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"}

The column data type is jsonb. I am trying to query it using a statement like. Like that:

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

I was looking for support jsonband there seems to be no operator like. If anyone has any advice.

Answer 1

If the data column is a text type, use ->>when creating:

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

If it is already json:

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

Answer 2

Another option that may be sufficient for other people who have found this page is to simply cast the column to a text type. for instance

select * from module_data where data::text like '%Board%'

, json , , .

Answer 3

The following works for columns of type JSONB:

select * from table_name
where 
column_name::text like '%Something%'

Found a good article about more examples and implementations: https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/

Hope this helps!

Answer 4

I think it should be like

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

then only it worked for me.