POSGRESQL 13.4 JSON聚合帮助

Question

我正在尝试将多个SQL行合并为一个大的JSON数组。到目前为止，我有此查询...

SELECT asset_id, jsonb_build_object('vender',vendor,'family',family,'name',name,'version',version,'type',type,'cpe',cpe) as software 
from dim_asset_software 
where asset_id = '1214';

我得到了此输出...

     asset_id |                                                                                software                                                                                
----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     1214 | {"cpe": null, "name": "Wireshark", "type": "<unknown>", "family": null, "vender": "Wireshark", "version": "2.6.3"}
     1214 | {"cpe": null, "name": "WinPcap 4.1.3", "type": "<unknown>", "family": null, "vender": "Riverbed Technology, Inc.", "version": "4.1.0.2980"}
     1214 | {"cpe": null, "name": ".NET Framework 4.6.2 Client Profile", "type": "<unknown>", "family": ".NET Framework", "vender": "Microsoft", "version": null}
     1214 | {"cpe": null, "name": "Internet Information Services", "type": "Internet Server", "family": "Internet Information Services", "vender": "Microsoft", "version": "10.0"}
     1214 | {"cpe": null, "name": ".NET Framework 4.6.2", "type": "<unknown>", "family": ".NET Framework", "vender": "Microsoft", "version": null}
     1214 | {"cpe": null, "name": "Windows Media Player", "type": "Media Client", "family": "Windows Media Player", "vender": "Microsoft", "version": "12.0.10011.16384"}
     1214 | {"cpe": null, "name": "Internet Explorer", "type": "Internet Client", "family": "Internet Explorer", "vender": "Microsoft", "version": "11.4350.14393.0"}
     1214 | {"cpe": null, "name": "MSXML", "type": "<unknown>", "family": "MSXML", "vender": "Microsoft", "version": "8.110.14393.4704"}
     1214 | {"cpe": null, "name": "Rapid7 InsightVM Scan Assistant v", "type": "<unknown>", "family": null, "vender": "Rapid7", "version": "1.0.0"}
     1214 | {"cpe": null, "name": "MSXML", "type": "<unknown>", "family": "MSXML", "vender": "Microsoft", "version": "6.30.14393.5006"}
     1214 | {"cpe": null, "name": "HTML Document", "type": "Browser Add-On", "family": null, "vender": null, "version": "11.0.14393.5066"}

如果可能的话，我正在尝试将所有软件信息放入单个嵌套的JSON行中。示例：

  asset_id |                                                                                software                                                                                
----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     1214 | [{"cpe": null, "name": "Wireshark", "type": "<unknown>", "family": null, "vender": "Wireshark", "version": "2.6.3"}, {"cpe": null, "name": "WinPcap 4.1.3", "type": "<unknown>", "family": null, "vender": "Riverbed Technology, Inc.", "version": "4.1.0.2980"}, {"cpe": null, "name": ".NET Framework 4.6.2 Client Profile", "type": "<unknown>", "family": ".NET Framework", "vender": "Microsoft", "version": null}, {"cpe": null, "name": "Internet Information Services", "type": "Internet Server", "family": "Internet Information Services", "vender": "Microsoft", "version": "10.0"}, {"cpe": null, "name": ".NET Framework 4.6.2", "type": "<unknown>", "family": ".NET Framework", "vender": "Microsoft", "version": null}, {"cpe": null, "name": "Windows Media Player", "type": "Media Client", "family": "Windows Media Player", "vender": "Microsoft", "version": "12.0.10011.16384"}, {"cpe": null, "name": "Internet Explorer", "type": "Internet Client", "family": "Internet Explorer", "vender": "Microsoft", "version": "11.4350.14393.0"}, {"cpe": null, "name": "MSXML", "type": "<unknown>", "family": "MSXML", "vender": "Microsoft", "version": "8.110.14393.4704"}, {"cpe": null, "name": "Rapid7 InsightVM Scan Assistant v", "type": "<unknown>", "family": null, "vender": "Rapid7", "version": "1.0.0"}, {"cpe": null, "name": "MSXML", "type": "<unknown>", "family": "MSXML", "vender": "Microsoft", "version": "6.30.14393.5006"}, {"cpe": null, "name": "HTML Document", "type": "Browser Add-On", "family": null, "vender": null, "version": "11.0.14393.5066"}]

谢谢！

Answer 1

这似乎是这样做的。如果您看到问题，请告诉我。谢谢。

SELECT asset_id, json_agg(temp_software) AS software
from dim_asset_software, jsonb_build_object('vender',vendor,'family',family,'name',name,'version',version,'type',type,'cpe',cpe) as temp_software
where asset_id = '1214'
GROUP by asset_id;