主页 / dba / 问题 / 123132
Accepted
ervazu
Asked: 2015-12-08 11:52:51 +0800 CST

如何将 JSON 值上的连接运算符与 AS 结合起来

  • 772

我正在尝试将多个 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，或者可能有更好的方法来做到这一点，因为连接运算符也不完美。

postgresql json

1 个回答

  1. Best Answer

    不完全确定您在问什么，但在 Postgres 9.3 或更高版本中，您可以使用json_agg()将整个表转换为 JSON 记录数组。准备单个子查询中的行并将整行提供给json_agg()使用表别名：

    像您显示的多层嵌套子选择似乎没有必要。

    简单的例子：

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

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

    询问：

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

    结果：

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

    SQL小提琴。

    • 1

相关问题