Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE prizes(id int, tuple json);
INSERT INTO prizes VALUES
  (1 , '{"year":"2021","category":"chemistry","laureates":[{"id": "1002", "firstname": "Benjamin", "surname": "List", "motivation": "\"for the development of asymmetric organocatalysis\"", "share": "2"}, {"id": "1003", "firstname": "David", "surname": "MacMillan", "motivation": "\"for the development of asymmetric organocatalysis\"", "share": "2"}],"reason":null}')
, (2 , '{"year":"2021","category":"literature","laureates":[{"id": "1004", "firstname": "Abdulrazak", "surname": "Gurnah", "motivation": "\"for his uncompromising and compassionate penetration of the effects of colonialism and the  fate of the refugee in the gulf between cultures and continents\"", "share": "1"}],"reason":null}')
-- tuple with single laureate (object instead of array)
, (10, '{"year":"2021","category":"singularity","laureates":{"id": "666", "firstname": "Single", "surname": "Guy", "motivation": "\"meh\"", "share": "1"},"reason":null}')
-- tuple with null laureate
, (11, '{"year":"2021","category":"nihilism","laureates":null,"reason":null}')
-- tuple with *no* laureate
, (12, '{"year":"2021","category":"void","reason":null}')
;

CREATE TABLE



INSERT 0 5


-- split cases with json_array_elements() (Postgres 9.3+)
SELECT id, tuple->'year' AS year, tuple->'category' AS category, json_array_elements(tuple->'laureates') AS r
FROM   prizes
WHERE  json_typeof(tuple->'laureates') = 'array'
UNION ALL
SELECT id, tuple->'year' AS year, tuple->'category' AS category, tuple->'laureates'
FROM   prizes
WHERE  json_typeof(tuple->'laureates') IS DISTINCT FROM 'array';


id
year
category
r


1
"2021"
"chemistry"
{"id": "1002", "firstname": "Benjamin", "surname": "List", "motivation": ""for the development of asymmetric organocatalysis"", "share": "2"}

1
"2021"
"chemistry"
{"id": "1003", "firstname": "David", "surname": "MacMillan", "motivation": ""for the development of asymmetric organocatalysis"", "share": "2"}

2
"2021"
"literature"
{"id": "1004", "firstname": "Abdulrazak", "surname": "Gurnah", "motivation": ""for his uncompromising and compassionate penetration of the effects of colonialism and the  fate of the refugee in the gulf between cultures and continents"", "share": "1"}

10
"2021"
"singularity"
{"id": "666", "firstname": "Single", "surname": "Guy", "motivation": ""meh"", "share": "1"}

11
"2021"
"nihilism"
null

12
"2021"
"void"
null



SELECT 6


-- No split with jsonb_path_query(), but rows without laureate are eliminated. (Postgres 12+)
SELECT id, tuple->>'year' AS year, tuple->>'category' AS category, jsonb_path_query((tuple->'laureates')::jsonb, '$[*]') AS r
FROM   prizes;


id
year
category
r


1
2021
chemistry
{"id": "1002", "share": "2", "surname": "List", "firstname": "Benjamin", "motivation": ""for the development of asymmetric organocatalysis""}

1
2021
chemistry
{"id": "1003", "share": "2", "surname": "MacMillan", "firstname": "David", "motivation": ""for the development of asymmetric organocatalysis""}

2
2021
literature
{"id": "1004", "share": "1", "surname": "Gurnah", "firstname": "Abdulrazak", "motivation": ""for his uncompromising and compassionate penetration of the effects of colonialism and the  fate of the refugee in the gulf between cultures and continents""}

10
2021
singularity
{"id": "666", "share": "1", "surname": "Guy", "firstname": "Single", "motivation": ""meh""}

11
2021
nihilism
null



SELECT 5


-- No split with jsonb_path_query() and all rows included. (Postgres 12+)
SELECT p.id, p.tuple->>'year' AS year, p.tuple->>'category' AS category, l.r
FROM   prizes p
LEFT   JOIN LATERAL jsonb_path_query((tuple->'laureates')::jsonb, '$[*]', '{"foo":"bar"}') AS l(r) ON true;


id
year
category
r


1
2021
chemistry
{"id": "1002", "share": "2", "surname": "List", "firstname": "Benjamin", "motivation": ""for the development of asymmetric organocatalysis""}

1
2021
chemistry
{"id": "1003", "share": "2", "surname": "MacMillan", "firstname": "David", "motivation": ""for the development of asymmetric organocatalysis""}

2
2021
literature
{"id": "1004", "share": "1", "surname": "Gurnah", "firstname": "Abdulrazak", "motivation": ""for his uncompromising and compassionate penetration of the effects of colonialism and the  fate of the refugee in the gulf between cultures and continents""}

10
2021
singularity
{"id": "666", "share": "1", "surname": "Guy", "firstname": "Single", "motivation": ""meh""}

11
2021
nihilism
null

12
2021
void
null



SELECT 6

id	year	category	r
1	"2021"	"chemistry"	{"id": "1002", "firstname": "Benjamin", "surname": "List", "motivation": ""for the development of asymmetric organocatalysis"", "share": "2"}
1	"2021"	"chemistry"	{"id": "1003", "firstname": "David", "surname": "MacMillan", "motivation": ""for the development of asymmetric organocatalysis"", "share": "2"}
2	"2021"	"literature"	{"id": "1004", "firstname": "Abdulrazak", "surname": "Gurnah", "motivation": ""for his uncompromising and compassionate penetration of the effects of colonialism and the fate of the refugee in the gulf between cultures and continents"", "share": "1"}
10	"2021"	"singularity"	{"id": "666", "firstname": "Single", "surname": "Guy", "motivation": ""meh"", "share": "1"}
11	"2021"	"nihilism"	null
12	"2021"	"void"	null

id	year	category	r
1	2021	chemistry	{"id": "1002", "share": "2", "surname": "List", "firstname": "Benjamin", "motivation": ""for the development of asymmetric organocatalysis""}
1	2021	chemistry	{"id": "1003", "share": "2", "surname": "MacMillan", "firstname": "David", "motivation": ""for the development of asymmetric organocatalysis""}
2	2021	literature	{"id": "1004", "share": "1", "surname": "Gurnah", "firstname": "Abdulrazak", "motivation": ""for his uncompromising and compassionate penetration of the effects of colonialism and the fate of the refugee in the gulf between cultures and continents""}
10	2021	singularity	{"id": "666", "share": "1", "surname": "Guy", "firstname": "Single", "motivation": ""meh""}
11	2021	nihilism	null