时间戳

首页 > 解决方案 > 带有伪行的 json_agg()

json - 带有伪行的 json_agg()

问题描述

CREATE TABLE content
(
    code          INTEGER NOT NULL
  , nameid        TEXT    NOT NULL
)

INSERT INTO content(code, nameid) VALUES
  ( 0, 'Support')
, ( 1, 'Adrenaline')
, ( 2, 'Aquapark')
;

需要将所有内容选择为名为“content”的 JSON 数组对象，其中伪行代码 = '' 和 nameid = 'All'

我做了什么：
1）简单的选择

SELECT 
   json_build_object('content', (SELECT json_agg(json_build_object(
    'code',   s.code::TEXT
  , 'nameid', s.nameid::TEXT
  ))
  FROM content s
  )
);

结果是

{
    "content": [{
            "code": "0",
            "nameid": "Support"
        },
        {
            "code": "1",
            "nameid": "Adrenaline"
        },
        {
            "code": "2",
            "nameid": "Aquapark"
        }
    ]
}

一切都很好，但是如何添加伪行？
2）我能做到的最好的

SELECT  
  json_build_object('content', json_build_array(
    json_build_object(
      'code',   ''
    , 'nameid', 'All'::TEXT),    
    (SELECT json_agg(json_build_object(
      'code',   s.code::TEXT
    , 'nameid', s.nameid::TEXT
  ))
  FROM content s
  ))
);

结果是

{
    "content": [{
            "code": "",
            "nameid": "All"
        },
        [{
                "code": "0",
                "nameid": "Support"
            },
            {
                "code": "1",
                "nameid": "Adrenaline"
            },
            {
                "code": "2",
                "nameid": "Aquapark"
            }
        ]
    ]
}

我们在数组中有数组，就像在 sql 和 json 中一样，但我不明白如何将 json_agg 与伪行结合起来。

标签： jsonpostgresqlaggregate-functions

解决方案

使用to_json()和union all：

select jsonb_build_object('content', json_agg(to_json))
from (
    select to_json(c)
    from (select '' as code, 'All' as nameid) c
    union all
    select to_json(c)
    from content c
    ) s

输出：

 {
    "content": [
        {
            "code": "",
            "nameid": "All"
        },
        {
            "code": 0,
            "nameid": "Support"
        },
        {
            "code": 1,
            "nameid": "Adrenaline"
        },
        {
            "code": 2,
            "nameid": "Aquapark"
        }
    ]
 }

派生表中具有别名的版本：

select jsonb_build_object('content', json_agg(codes))
from (
    select to_json(c) as codes
    from (select '' as code, 'All' as nameid) c
    union all
    select to_json(c)
    from content c
    ) s

推荐阅读