Postgres 15

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

CREATE TABLE tbl(
name     text,
detail   jsonb,
state    jsonb
);

INSERT INTO tbl VALUES
  ('apple' , '[{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }]',  '[{ "ap": "good", "op2": "bad" }]')
, ('orange', '[{ "code": "156", "color": "red" }, { "code": "235", "color": "blue" }]', '[{ "op": "bad", "op2": "best" }]')
, ('lemon' , '[{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }]', '[{ "cp": "best", "op2": "good" }]')
;

INSERT INTO tbl VALUES
  ( 'single', '[{ "code": "666", "color": "black" }]',  '[{ "ap": "evil", "op2": "worst" }]') 
, ( 'empty' , '[]',  '[{ "ap": "good", "op2": "bad" }]')
, ( 'void'  , null,  '[{ "ap": "good", "op2": "bad" }]')
;

CREATE TABLE



INSERT 0 3



INSERT 0 3


SELECT name, jsonb_array_elements(detail) AS detail, state
FROM   tbl;


name
detail
state


apple
{"code": "156", "color": "red"}
[{"ap": "good", "op2": "bad"}]

apple
{"code": "156", "color": "blue"}
[{"ap": "good", "op2": "bad"}]

orange
{"code": "156", "color": "red"}
[{"op": "bad", "op2": "best"}]

orange
{"code": "235", "color": "blue"}
[{"op": "bad", "op2": "best"}]

lemon
{"code": "156", "color": "red"}
[{"cp": "best", "op2": "good"}]

lemon
{"code": "156", "color": "blue"}
[{"cp": "best", "op2": "good"}]

single
{"code": "666", "color": "black"}
[{"ap": "evil", "op2": "worst"}]



SELECT 7


SELECT t.name, e.detail, t.state
FROM   tbl t
LEFT   JOIN LATERAL jsonb_array_elements(t.detail) AS e(detail) ON true;


name
detail
state


apple
{"code": "156", "color": "red"}
[{"ap": "good", "op2": "bad"}]

apple
{"code": "156", "color": "blue"}
[{"ap": "good", "op2": "bad"}]

orange
{"code": "156", "color": "red"}
[{"op": "bad", "op2": "best"}]

orange
{"code": "235", "color": "blue"}
[{"op": "bad", "op2": "best"}]

lemon
{"code": "156", "color": "red"}
[{"cp": "best", "op2": "good"}]

lemon
{"code": "156", "color": "blue"}
[{"cp": "best", "op2": "good"}]

single
{"code": "666", "color": "black"}
[{"ap": "evil", "op2": "worst"}]

empty
null
[{"ap": "good", "op2": "bad"}]

void
null
[{"ap": "good", "op2": "bad"}]



SELECT 9

name	detail	state
apple	{"code": "156", "color": "red"}	[{"ap": "good", "op2": "bad"}]
apple	{"code": "156", "color": "blue"}	[{"ap": "good", "op2": "bad"}]
orange	{"code": "156", "color": "red"}	[{"op": "bad", "op2": "best"}]
orange	{"code": "235", "color": "blue"}	[{"op": "bad", "op2": "best"}]
lemon	{"code": "156", "color": "red"}	[{"cp": "best", "op2": "good"}]
lemon	{"code": "156", "color": "blue"}	[{"cp": "best", "op2": "good"}]
single	{"code": "666", "color": "black"}	[{"ap": "evil", "op2": "worst"}]