1

I have a table of the form:

                          Table "public.items"
     Column      |           Type           |         Modifiers         
-----------------+--------------------------+---------------------------
 item_id         | character varying        | not null
 content         | character varying        | 
 tags            | jsonb                    | 
 body            | jsonb                    |

The body field contains data like these:

[{"text": "As a child home ..."}, 
{"text": "Great post. Watch this }]

What query is needed to find documents by word in the text section?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Sergey Kozlov
  • 452
  • 5
  • 17

2 Answers2

2

The standard (and most adequate) approach is the one provided by @Dan in the other answer, namely to treat a jsonb column as such an parse it using the operators -> and ->>.

An alternative to that would be to cast the column to text and go from there, e.g. body::text:

SELECT * FROM public.items WHERE body::TEXT LIKE '%child%';
SELECT * FROM public.items WHERE body::TEXT SIMILAR TO '%child%';

In case you don't want it to be case sensitive, convert both strings before comparing them, for instance using LOWER or UPPER (see documentation):

SELECT * FROM items WHERE LOWER(body::TEXT) LIKE LOWER('%CHILD%');

As suggested by Dan in the comments, ILIKE is another elegant way for dealing case insensitive queries:

SELECT * FROM items WHERE body::TEXT ILIKE '%child%'

Note: both approaches rely on a proper index to work satisfactory, otherwise the query might get very slow. See this other answer with a GIN index

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • For case insensitive it's better to use `ILIKE` instead of using a function for every sentence. – Dan Apr 11 '18 at 22:56
  • Also, you can access a JSON attribute directly without having to cast it at text. See my answer. – Dan Apr 11 '18 at 23:02
  • you're right, I totally forgot ILIKE.. thanks for the kind message. I'll update my answer accordingly. – Jim Jones Apr 11 '18 at 23:07
2

You can do the seach using jsonb functions. Lets say you have this:

item_id | body
--------+---------------------------------------------------------------
1       | [{"text":"aSdf aSdf"},{"text":"12 41f"},{"text":"1 31s sf"}]
2       | [{"text":"31fa3"},{"text":"3f43f"}]
3       | [{"text":"l8412"},{"text":"52%$3d1f"},{"text":"agasd as3"}]
4       | [{"text":"i8i23"}]

You can search for every element in your jsonb using:

SELECT * FROM (SELECT t.id,elem.*
FROM public.items t,jsonb_array_elements(t.body) AS elem)json_vals
WHERE value->>'text' ILIKE '%s%'

This will return the next result because the query uses ILIKE:

item_id | value
--------+----------------------
1       | {"text":"aSdf aSdf"}
1       | {"text":"1 31s sf"}
3       | {"text":"agasd as3"}

If you just need the id, you can change the * in the query for item_id and use DISTINCT. But with this query you can access every JSON in the record without having to cast it as text.

NOTE: if you just query SELECT t.id,elem.* FROM public.items t,jsonb_array_elements(t.body) AS elem you get every element in a row:

itme_id | value
--------+--------------------
1       | {"text":"aSdf aSdf"}
1       | {"text":"12 41f"}
1       | {"text":"1 31s sf"}
2       | {"text":"31fa3"}
2       | {"text":"3f43f"}
3       | {"text":"l8412"}
3       | {"text":"52%$3d1f"}
3       | {"text":"agasd as3"}
4       | {"text":"i8i23"}
Dan
  • 1,771
  • 1
  • 11
  • 19