PostgreSQL joining using JSONB

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->>'childr

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.

StackExchange Database Administrators Q#83932, answer score: 36