Postgresql – jsonb and numeric precision and scale

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.

From docs (http://www.postgresql.org/docs/9.4/static/datatype-json.html) try to use expression index:

CREATE INDEX idx_jsonthings_names ON jsonthings USING gin ((d -> 'name'));
SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';