How to combine concatenate operator on JSON values with AS

Question

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.

Answer 1

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 to json_agg() using the table alias:

Multiple layers of nested subselects like you display seem uncalled for.

Simple example:

CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  foo    text
);

INSERT INTO tbl VALUES
  (1, 'First' )
, (2, 'Second');

Query:

SELECT json_agg(sub)
FROM  (
   SELECT tbl_id        AS some_id
        , foo           AS bar
        , tbl_id || foo AS some_derived_column
   FROM  tbl
   ) sub;

Result:

[{"some_id":1,"bar":"First","some_derived_column":"1First"}, 
 {"some_id":2,"bar":"Second","some_derived_column":"2Second"}]

SQL Fiddle.