PostgreSQL jsonb nested pattern matching text search across multiple rows

Question

as stated in the title, I am attempting to do a query that preforms a full text search on multiple rows, agains a jsonb data type, with nested data, the problem is as follows:

CREATE TABLE books (id int primary key, title text, info jsonb);
INSERT INTO users (id, t, j) VALUES
(1, 'title 1', '{"Characters": [{"Name": "foo"}]}'),
(2, 'title 2', '{"Characters": [{"Name": "foo"},{"Name": "bar"}]}');
(3, 'title 3', '{"Characters": null}');

Question:

I would like to do a query for books, by the names of the characters within each book. For example query every book with a character named "foo". While the outline above is just an example my real life scenario calls for me to do a search on character's name using the "~*" operator.

So far im pretty stumped on this so any help would be appreciated, thanks.

Answer 1

with cte (id, title, Name) as
(
select id, title, jsonb_array_elements(info->'Characters')->>'Name' as Name
from books
where (info->>'Characters')::text is not null
)
select id, title, Name 
from cte
where Name like 'fo%';

Check it here: http://rextester.com/TVKWC17198