Postgresql json like query

Question

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.

The `like` operator exists, but a `title` column does not. It is a *property/attribute* of the `data` column, thus [you'll need to extract it's value](https://www.postgresql.org/docs/current/static/functions-json.html) to compare it: `data ->> 'title'` or `data #>> ARRAY['title']` — pozs, Mar 21 '17 at 10:05

Gurwinder Singh · Accepted Answer · 2017-03-21T04:36:29.527

149

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%'

edited Mar 21 '17 at 04:36

answered Mar 21 '17 at 04:20

Gurwinder Singh

38,557
6
51
76

Awesome, Thank you. Its my first time using jsonb so I really appreciate the tip – adviner Mar 21 '17 at 17:24
8

Reminder to those who reading the answer: Use query parameter to avoid SQL injection. – baijum Oct 24 '17 at 16:54
1

@baijum what is query parameter? – jayarjo Jan 01 '21 at 14:33

Deep Sehgal · Answer 2 · 2019-10-25T19:54:51.847

82

I found the following is more straight-forward and easier for jsonb type of columns:

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

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

Hope it helps!

edited Oct 25 '19 at 19:54

answered Aug 19 '19 at 18:50

Deep Sehgal

1,052
9
13

1

This will also search keys, so be careful using this. Only use this if you can guarantee that keys won't have search term. – user961954 May 14 '20 at 01:26
If you want to search on the values only and not on the keys, you can try something like select * from table_name where array_to_string( array(select value from jsonb_each_text( )), ' ') like '%Something%' – jlfenaux Jun 05 '20 at 10:16
2

so much better for quick debugging – Peter F Aug 31 '20 at 13:49

score 16 · Answer 3 · answered Jul 31 '19 at 01:29

16

One other option which may be sufficient for other people who've found this page is to just cast the column to text type. Eg

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

Note though, this will search over the entire json and should only be used if you can guarantee the other fields won't be a problem.

answered Jul 31 '19 at 01:29

nevster

6,271
7
35
42

1

This will also search keys, so be careful using this. Only use this if you can guarantee that keys won't have search term. – user961954 May 14 '20 at 01:25
2

I needed to search keys so perfect for me :) – Titozzz May 22 '20 at 10:25
This is so good for one-off searches. +1 – Shautieh Aug 09 '23 at 13:01

score 2 · Answer 4 · answered Sep 05 '18 at 07:16

2

I Think it should be like

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

then only it worked for me.

answered Sep 05 '18 at 07:16

Karansing

47
6

Postgresql json like query

4 Answers4

Linked