'PosgreSQL 13.4 json aggregation assistance

I''m trying to merge multiple SQL rows into a large JSON array. So far I have this query...

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';

I get this output...

     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"}

I'm trying to put all the software information into a single nested json row if that is possible. Example:

  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"}]

Thank you!

postgresqlaggregate-functions


Solution 1:[1]

This seemed to do it. Please let me know if you see an issue. Thank you.

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JeremyP

Related Questions