Expanding jsob array in PostgreSQL to produce analytics

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?

instead of

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

>Solution :

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