ervazu's questions

我正在尝试将多个 JSON 对象组合成 Postgres 中的一个大对象。

我可以用一个额外的东西来做到这一点SELECT row_to_json（就像我的第一个例子一样），但是这样它会创建一个额外的对象，我不想要这个对象，因为这个对象已经被array_agg它包裹起来了。但是，array_agg不接受多列。所以我尝试用连接运算符来解决它，但我不能用AS它，这是给对象正确的键所必需的。（并且数据类型变为text，哪个to_json函数转义，这并不完美，但我可以忍受）。

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;

这导致

[
  {
    "ThisKeyShouldBeGone": {
      "geometry": {
        "type": "MultiLineString",
        "coordinates": [
          [
            [..]
          ]
        ]
      },
      "properties": {
        [..]
      },
      "type": "Feature"
    }
  },
  {
    "ThisKeyShouldBeGone": {
      "geometry": [..]

因此，为了摆脱“ThisKeyShouldBeGone”键，我尝试使用连接运算符，但这样我不能使用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;

那么有没有一种方法可以将连接运算符与 结合起来AS，或者可能有更好的方法来做到这一点，因为连接运算符也不完美。

我很抱歉这个模糊的标题，但我根本不知道合适的词来描述这个。

我有这个查询将一堆列转换为一个可以正常工作的对象：

SELECT row_to_json(t)
FROM (
  SELECT type, properties, geometry FROM "bgbCargoMinardJSON"
) t

但是，我想将属于某个类别的对象分组到一个数组中。此类别由我的表中名为“cargoProductId”的第四列定义。该数组应以“cargoProductId”的值作为键。所以：

"961":[
{"type":"Feature",....
{"type":"Feature",....
{"type":"Feature",....
],
"962":[
.....
]

所以在过去的 1 1/2 小时左右，我一直在努力解决这个问题。我真的不知道该怎么做。这就是我现在所拥有的：

SELECT array_agg(row_to_json(t))
FROM (
  SELECT type, properties, geometry FROM "bgbCargoMinardJSON"
) t) FROM "bgbCargoMinardJSON" GROUP BY "carProductId"