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.
"bgbCargoMinardSplit"
and, ideally some sample values for the table.