Substring search on JSON data (ERROR: cannot extract element from a scalar)

Question

I've been looking at this tutorial and this example trying to find a performant way to query by substring with JSON (<9.5)/JSONB (*9.5) data.

For example, I have this data:

CREATE TABLE public.foo
(
  row_id SERIAL PRIMARY KEY,
  data json
);

INSERT INTO foo VALUES (1,
  '{ "name": "Book the First", "author": "Bob", "text_entry": "White Cow Walked Over the Moon" } ');
INSERT INTO foo VALUES (2,
  '{ "name": "Book the Second", "author": "Charles", "text_entry": "Humptey Dumptey sat on the Moon" } ');
INSERT INTO foo VALUES (3,
  '{ "name": "Book the Third", "author": "Jim", "text_entry": "Red Fox jumped over Brown Dog" } ');

I'm looking for a way to search ONLY "text_entry" and return any case that has the sub-string "the Moon" (in this case, it would be id = 1 & 2). Expected Return:

text_entry
"White Cow Walked Over the Moon" ## has the substring "the Moon"
"Humptey Dumptey sat on the Moon" ## has the substring "the Moon"

So far my query looks like this:

    SELECT data->'text_entry'->'%the Moon%' AS query FROM foo;

ERROR:  cannot extract element from a scalar
********** Error **********

Is there any elegant way to query substrings in JSON/B?

Answer 1

I'm looking for a way to search ONLY "text_entry" and return any case that has the sub-string "the Moon"

SELECT data->>'text_entry' as query
FROM foo
WHERE data->>'text_entry' LIKE '%the Moon%';

->

query              
---------------------------------
White Cow Walked Over the Moon
Humptey Dumptey sat on the Moon
(2 rows)