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
Advertisement
Answer
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)