Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl(id int, data jsonb);
INSERT INTO tbl VALUES
  (1, '[{"id": "11", "name":"entry11"},{"id": "12", "name": "entry12"}]'::jsonb)
, (2, '[{"id": "21", "name":"entry21"},{"id": "22", "name": "entry22"}]')
, (3, '[]')  -- empty array!
;

CREATE TABLE



INSERT 0 3


-- Original query - note missing row 3!
SELECT id
     , jsonb_array_elements(data) -> 'id'   AS id
     , jsonb_array_elements(data) -> 'name' AS name
FROM   tbl;


id
id
name


1
"11"
"entry11"

1
"12"
"entry12"

2
"21"
"entry21"

2
"22"
"entry22"



SELECT 4


-- exact equivalent to illustrate what's going on
SELECT t.id, x.i->'id' AS id, x.n->'name' AS name
FROM   tbl t
CROSS  JOIN LATERAL ROWS FROM (jsonb_array_elements(t.data), jsonb_array_elements(t.data)) x(i, n);


id
id
name


1
"11"
"entry11"

1
"12"
"entry12"

2
"21"
"entry21"

2
"22"
"entry22"



SELECT 4


-- Sane alternative, still filtering row 3:
SELECT t.id, x->'id' AS id, x->'name' AS name
FROM   tbl t
CROSS  JOIN LATERAL jsonb_array_elements(t.data) x;


id
id
name


1
"11"
"entry11"

1
"12"
"entry12"

2
"21"
"entry21"

2
"22"
"entry22"



SELECT 4


-- The same with minimal syntax:
SELECT id, x->'id' AS id, x->'name' AS name
FROM   tbl, jsonb_array_elements(data) x;


id
id
name


1
"11"
"entry11"

1
"12"
"entry12"

2
"21"
"entry21"

2
"22"
"entry22"



SELECT 4


-- Safe alternative preserving all rows:
SELECT t.id, x->'id' AS id, x->'name' AS name
FROM   tbl t
LEFT   JOIN LATERAL jsonb_array_elements(t.data) x ON true;


id
id
name


1
"11"
"entry11"

1
"12"
"entry12"

2
"21"
"entry21"

2
"22"
"entry22"

3
null
null



SELECT 5


-- Better yet with jsonb_populate_recordset()!

-- create once if not registered,yet:
CREATE TYPE my_rowtype AS (id int, name text);

-- Then:
SELECT t.id, x.id, x.name
FROM   tbl t
LEFT   JOIN LATERAL jsonb_populate_recordset(null::my_rowtype, t.data) x ON true;

CREATE TYPE




id
id
name


1
11
entry11

1
12
entry12

2
21
entry21

2
22
entry22

3
null
null



SELECT 5
id	id	name
1	"11"	"entry11"
1	"12"	"entry12"
2	"21"	"entry21"
2	"22"	"entry22"
3	null	null