Postgres 9.2
The benefit of the new feature is two-fold. Columns of type json
verify the validity of its content so that what's in the column is automatically valid JSON and you get an error if you try to write anything else to it.
And you have basic functions to create valid JSON on the fly from rows or arrays - which is a very common use-case.
I quote Andrew Dunstan on the pgsql-hackers list:
At some stage there will possibly be some json-processing (as opposed
to json-producing) functions, but not in 9.2.
I used that quote before under this related question on SO.
Postgres 9.3
.. finally brings a number of functions and operators. Check out the manual page for JSON functions.
Related answer on SO:
@Will put up blog post. See comment below.
Postgres 9.4
Be sure to check out the new jsonb
type with a host of new functionality.
Above all, the decomposed, binary storage allows for smaller storage on disk and an equality operator for jsonb
(unlike json
), which makes a number of additional operations possible (like DISTINCT
or a UNIQUE
index).
Yet more functions have been added for both json
and jsonb
. json_to_record()
, json_to_recordset()
etc. More in the release notes.
Alright, so you seem to want to create a graph from a join tree. That's a pretty natural thing to want to do.
It's not as easy as it should be in Pg, mainly because of the lack of support for column-aliases in the record
pseudo-type.
Here's what I came up with:
SELECT row_to_json(r, true)
FROM (
SELECT
d.id,
d.name,
json_agg(c_row) AS components
FROM devices d
INNER JOIN device_components dc ON (dc.deviceid = d.id)
INNER JOIN (
SELECT
c.id,
c.name,
json_agg(m) AS manufacturers
FROM components c
INNER JOIN component_manufacturers cm ON (cm.componentid = c.id)
INNER JOIN manufacturers m ON (cm.manufacturerid = m.id)
GROUP BY c.id
) c_row ON (c_row.id = dc.componentid)
GROUP BY d.id
) r(id, name, component);
The general idea here is, at every level of object nesting where you're producing an array of objects, use json_agg
in a group_by
. The json_agg
function implicitly calls row_to_json
to transform a rowtype into json. In the SELECT
clause specify aliases for the synthetic columns so the json key names are correct when the row is fed into the outer level.
As the outer level isn't aggregated, use row_to_json
over a subquery instead of using json_agg
. If you want a single json result instead of a set of json rows, you can change row_to_json
to json_agg
in the outer level.
I've only tested on 9.3, as that's what I have installed and sqlfiddle seems to be having some issues.
Update: Unfortunately json_agg
doesn't exist on 9.2, it was added in 9.3. However, this is a simple enough case that you can just use array_agg
instead, so this should work in 9.2:
SELECT row_to_json(r, true)
FROM (
SELECT
d.id,
d.name,
array_agg(c_row) AS components
FROM devices d
INNER JOIN device_components dc ON (dc.deviceid = d.id)
INNER JOIN (
SELECT
c.id,
c.name,
array_agg(m) AS manufacturers
FROM components c
INNER JOIN component_manufacturers cm ON (cm.componentid = c.id)
INNER JOIN manufacturers m ON (cm.manufacturerid = m.id)
GROUP BY c.id
) c_row ON (c_row.id = dc.componentid)
GROUP BY d.id
) r(id, name, component);
Ideally PostgreSQL would support this sort of query more naturally, without the subqueries, but I think we'd need a new join type, or at least some functions to use with lateral queries. It all plans out into a civilised query plan anyway (though it'd be better with suitable indexes):
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on r (cost=1050.26..1282.94 rows=720 width=106)
-> GroupAggregate (cost=1050.26..1273.94 rows=720 width=188)
-> Merge Join (cost=1050.26..1226.42 rows=7704 width=188)
Merge Cond: (d.id = dc.deviceid)
-> Index Scan using devices_pkey on devices d (cost=0.15..58.95 rows=720 width=82)
-> Sort (cost=1050.11..1069.37 rows=7704 width=110)
Sort Key: dc.deviceid
-> Merge Join (cost=403.16..552.77 rows=7704 width=110)
Merge Cond: (c_row.id = dc.componentid)
-> Subquery Scan on c_row (cost=253.38..285.63 rows=720 width=110)
-> GroupAggregate (cost=253.38..278.43 rows=720 width=286)
-> Sort (cost=253.38..258.73 rows=2140 width=286)
Sort Key: c.id
-> Hash Join (cost=44.75..135.00 rows=2140 width=286)
Hash Cond: (cm.manufacturerid = m.id)
-> Hash Join (cost=26.20..87.03 rows=2140 width=86)
Hash Cond: (cm.componentid = c.id)
-> Seq Scan on component_manufacturers cm (cost=0.00..31.40 rows=2140 width=8)
-> Hash (cost=17.20..17.20 rows=720 width=82)
-> Seq Scan on components c (cost=0.00..17.20 rows=720 width=82)
-> Hash (cost=13.80..13.80 rows=380 width=208)
-> Seq Scan on manufacturers m (cost=0.00..13.80 rows=380 width=208)
-> Sort (cost=149.78..155.13 rows=2140 width=8)
Sort Key: dc.componentid
-> Seq Scan on device_components dc (cost=0.00..31.40 rows=2140 width=8)
(25 rows)
Best Answer
You would need the
->>
operator instead:But these operators were added with Postgres 9.3. Postgres 9.2 is just too old (and unsupported by now). It had barely more than the data type
json
itself. There seems to be a third-party extension "Json Enhancements for PostgreSQL 9.2":But I have never tried it and would much rather upgrade to a current, supported version of Postgres instead. See: