1

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.

1
  • You should provide your version of Postgres, the table definition of "bgbCargoMinardSplit" and, ideally some sample values for the table. Dec 7, 2015 at 20:45

1 Answer 1

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.

1
  • Thanks Erwin. I can understand that my question is a bit vague. I had a fresh look at it this morning, and the solution seemed to be quite easy. I removed some subselects (as you suggested) and I got it. I probably got a bit confused by them myself.
    – ervazu
    Dec 9, 2015 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.