Expanding jsob array in PostgreSQL to produce analytics

Question

Imagine that we have the following table using jsonb on PostgreSQL:

create table writer
(
  "firstName" varchar,
  "surName"   varchar,
  books       jsonb
);

And the following data is available:

INSERT INTO public.writer ("firstName", "surName", books) VALUES ('William', 'Shakespeare', '[{"name": "Hamlet"}, {"name": "Romeo and Juliet"}]');
INSERT INTO public.writer ("firstName", "surName", books) VALUES ('Agatha', 'Christie', '[{"name": "Hercule Poirot"}, {"name": "Miss Marple"}]');

Is it possible to expand the JSON array to 2 columns similarly to what PowerBI expand does and get the following result?

firstName	surName	bookName
William	Shakespeare	Hamlet
William	Shakespeare	Juliet
Agatha	Christie	Hercule Poirot
Agatha	Christie	Miss Marple

instead of

firstName	surName	books
William	Shakespeare	[{"name": "Hamlet"}, {"name": "Romeo and Juliet"}]
Agatha	Christie	[{"name": "Hercule Poirot"}, {"name": "Miss Marple"}]

Sample DB: http://sqlfiddle.com/#!17/87ca94/2

Answer 1

You can use jsonb_array_elements() to get one row per array element:

select w."firstName", w."surName", b.book ->> 'name' as book_name
from writer w
  cross join jsonb_array_elements(books) as b(book)

Online example