PostgreSQL joining using JSONB

Question

I have this SQL:

CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);
INSERT INTO test(data) VALUES
 ('{"parent":null,"children":[2,3]}'),
 ('{"parent":1, "children":[4,5]}'),
 ('{"parent":1, "children":[]}'),
 ('{"parent":2, "children":[]}'),
 ('{"parent":2, "children":[]}');

That would give:

 id | data 
----+--------------------------------------
 1 | {"parent": null, "children": [2, 3]}
 2 | {"parent": 1, "children": [4, 5]}
 3 | {"parent": 1, "children": []}
 4 | {"parent": 2, "children": []}
 5 | {"parent": 2, "children": []}

When doing normal one to many, it would show something like this:

SELECT * 
FROM test x1
 LEFT JOIN test x2
 ON x1.id = (x2.data->>'parent')::INT;
 id | data | id | data 
----+--------------------------------------+----+-----------------------------------
 1 | {"parent": null, "children": [2, 3]} | 2 | {"parent": 1, "children": [4, 5]}
 1 | {"parent": null, "children": [2, 3]} | 3 | {"parent": 1, "children": []}
 2 | {"parent": 1, "children": [4, 5]} | 4 | {"parent": 2, "children": []}
 2 | {"parent": 1, "children": [4, 5]} | 5 | {"parent": 2, "children": []}
 5 | {"parent": 2, "children": []} | | 
 4 | {"parent": 2, "children": []} | | 
 3 | {"parent": 1, "children": []} | | 

How to join based on children (using LEFT JOIN or WHERE IN)? I've tried:

SELECT data->>'children' FROM test;
 ?column? 
----------
 [2, 3]
 [4, 5]
 []
 []
 []
SELECT json_array_elements((data->>'children')::TEXT) FROM t...
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT json_array_elements((data->>'children')::JSONB) FROM ...
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT json_to_record((data->>'children')::JSON) FROM test;
ERROR: function returning record called in context that cannot accept type record
HINT: Try calling the function in the FROM clause using a column definition list.
SELECT * FROM json_to_record((test.data->>'children')::JSON);
ERROR: missing FROM-clause entry for table "test"
LINE 1: SELECT * FROM json_to_record((test.data->>'children')::JSON)...

Answer 1

This would be more efficient:

With jsonb and jsonb_array_elements_text() in pg 9.4+

EXPLAIN 
SELECT p.id AS p_id, p.data
 , c.id AS c_id, c.data
FROM test p
LEFT JOIN LATERAL jsonb_array_elements_text(p.data->'children') pc(child) ON TRUE
LEFT JOIN test c ON c.id = pc.child::int;

db<>fiddle here

About jsonb_array_elements_text():

• How to turn JSON array into Postgres array?

Use the -> operator instead of ->> in the reference to children. The way you have it, you'd first cast json / jsonb to text and then back to json.

The clean way to call a set-returning function is LEFT [OUTER] JOIN LATERAL. This includes rows without children. To exclude those, change to a [INNER] JOIN LATERAL or CROSS JOIN - or the shorthand syntax with a comma:

, json_array_elements(p.data->'children') pc(child)

Avoiding duplicate column names in result.

With json and json_array_elements() in pg 9.3

SELECT p.id AS p_id, p.data AS p_data
 , c.id AS c_id, c.data AS c_data
FROM test p
LEFT JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE
LEFT JOIN test c ON c.id = pc.child::text::int;

_{Old sqlfiddle}

Aside: A normalized DB design with basic data types would be way more efficient for this.

Answer 2

Nevermind, I found the way

SELECT *
 FROM ( SELECT *, json_array_elements((data->>'children')::JSON) child FROM test) x1
 LEFT JOIN test x2
 ON x1.child::TEXT::INT = x2.id
;
 id | data | child | id | data
----+--------------------------------------+-------+----+-----------------------------------
 1 | {"parent": null, "children": [2, 3]} | 2 | 2 | {"parent": 1, "children": [4, 5]}
 1 | {"parent": null, "children": [2, 3]} | 3 | 3 | {"parent": 1, "children": []}
 2 | {"parent": 1, "children": [4, 5]} | 4 | 4 | {"parent": 2, "children": []}
 2 | {"parent": 1, "children": [4, 5]} | 5 | 5 | {"parent": 2, "children": []}
 QUERY PLAN 
-----------------------------------------------------------------------------------------------------------
 Hash Left Join (cost=37.67..4217.38 rows=123000 width=104)
 Hash Cond: ((((json_array_elements(((test.data ->> 'children'::text))::json)))::text)::integer = x2.id)
 -> Seq Scan on test (cost=0.00..643.45 rows=123000 width=36)
 -> Hash (cost=22.30..22.30 rows=1230 width=36)
 -> Seq Scan on test x2 (cost=0.00..22.30 rows=1230 width=36)

or

SELECT *
 FROM test x1
 LEFT JOIN ( SELECT *, json_array_elements((data->>'children')::JSON) child FROM test) x2
 ON x1.id = x2.child::TEXT::INT
;
 id | data | id | data | child 
----+--------------------------------------+----+--------------------------------------+-------
 2 | {"parent": 1, "children": [4, 5]} | 1 | {"parent": null, "children": [2, 3]} | 2
 3 | {"parent": 1, "children": []} | 1 | {"parent": null, "children": [2, 3]} | 3
 4 | {"parent": 2, "children": []} | 2 | {"parent": 1, "children": [4, 5]} | 4
 5 | {"parent": 2, "children": []} | 2 | {"parent": 1, "children": [4, 5]} | 5
 1 | {"parent": null, "children": [2, 3]} | | | 
 QUERY PLAN 
-----------------------------------------------------------------------------------------------------------
 Hash Right Join (cost=37.67..4217.38 rows=123000 width=104)
 Hash Cond: ((((json_array_elements(((test.data ->> 'children'::text))::json)))::text)::integer = x1.id)
 -> Seq Scan on test (cost=0.00..643.45 rows=123000 width=36)
 -> Hash (cost=22.30..22.30 rows=1230 width=36)
 -> Seq Scan on test x1 (cost=0.00..22.30 rows=1230 width=36)