I'm trying to combine multiple JSON objects into one big object in Postgres.
I could do this with an extra SELECT row_to_json
(as in my first example), but this way it creates a extra object, which I don't want because this object is already created by array_agg
which is wrapped around it. However, array_agg
doesn't accept multiple columns. So I tried to solve it with the concatenation operator, but I can't use AS
with it, which is necessary to give the objects the right keys. (And the data type becomes text
, which to_json
function escapes, which is not perfect but I can live with it).
SELECT to_json(array_agg(g)) FROM (SELECT
(SELECT row_to_json(e) FROM
(SELECT ST_AsGeoJSON(ST_Union(cargogeom))::json AS geometry,
(SELECT row_to_json(d) FROM
(SELECT cargoid AS voyageid, voyages, cargovalues) d) AS properties,
to_json('Feature'::text) AS type) e) AS "ThisKeyShouldBeGone"
FROM (SELECT
cargoid,
cargogeom,
sum(cargonumvoyages) AS voyages,
sum(cargovalues) AS cargovalues
FROM "bgbCargoMinardSplit"
GROUP BY cargoid, cargogeom)
AS x
WHERE cargoid = 1000
GROUP BY cargoid, voyages, cargovalues
ORDER BY cargoid ) AS g;
cargoid,
cargogeom,
sum(cargonumvoyages) AS voyages,
sum(cargovalues) AS cargovalues
FROM "bgbCargoMinardSplit"
GROUP BY cargoid, cargogeom)
AS x
WHERE cargoid = 1000
GROUP BY cargoid, voyages, cargovalues
ORDER BY cargoid ) AS g;
Which results in
[
{
"ThisKeyShouldBeGone": {
"geometry": {
"type": "MultiLineString",
"coordinates": [
[
[..]
]
]
},
"properties": {
[..]
},
"type": "Feature"
}
},
{
"ThisKeyShouldBeGone": {
"geometry": [..]
So to get rid of the "ThisKeyShouldBeGone"-key I tried using concatenate operators, but this way I can't use AS
:
SELECT to_json(array_agg(g)) FROM (SELECT
(SELECT ST_AsGeoJSON(ST_Union(cargogeom))::json || ',' ||
(SELECT row_to_json(d) FROM
(SELECT cargoid AS voyageid, voyages, cargovalues) d) || ',' ||
to_json('Feature'::text) AS type)
FROM (SELECT
cargoid,
cargogeom,
sum(cargonumvoyages) AS voyages,
sum(cargovalues) AS cargovalues
FROM "bgbCargoMinardSplit"
GROUP BY cargoid, cargogeom)
AS x
WHERE cargoid = 1000
GROUP BY cargoid, voyages, cargovalues
ORDER BY cargoid ) AS g;
So is there a way I can combine the concatenate operator with AS
, or is there maybe a better way to do this, since the concatenate operator isn't perfect either.
Best Answer
Not exactly sure what you are asking, but in Postgres 9.3 or later you can use
json_agg()
to translate a whole table into a JSON array of records. Prepare the rows in a single subquery and feed whole rows tojson_agg()
using the table alias:Multiple layers of nested subselects like you display seem uncalled for.
Simple example:
Query:
Result:
SQL Fiddle.