On Mon, Mar 30, 2015 at 6:27 AM, Deven Phillips <deven.phillips@xxxxxxxxx> wrote: > I have a multi-table join which will return 1 row per "interface" and I > would like to aggregate the interfaces as a nested section on the resulting > JSON output. Could someone help me to figure out how to accomplish that? > > Example: > >> SELECT row_to_json(row) AS json >> FROM ( >> SELECT >> c.snt_code AS "snt_code", >> vdc.id AS "vdc", >> vm.uuid AS "uuid", >> vm.name AS "name", >> vm.os AS "os", >> vm.service_type AS "service_type", >> vm.template_name AS "template_name", >> vi.virtual_machine_id AS "vmid", >> vi.mac_address AS "mac_address", >> vi."order" AS "sort", >> ip.address AS "ip_address", >> ip.is_reserved AS "is_reserved", >> ip.is_gateway AS "is_gateway", >> vlan.vlan_id AS "vlan", >> false AS "is_backend" >> FROM customer c >> LEFT JOIN virtualdatacenter vdc ON c.id=vdc.customer_id >> LEFT JOIN virtualmachine vm ON vm.virtual_data_center_id=vdc.id >> LEFT JOIN virtualinterface vi ON vm.id=vi.virtual_machine_id >> INNER JOIN ipaddress ip ON vi.id=ip.virtual_interface_id >> INNER JOIN virtuallan vlan ON ip.network_id=vlan.id >> WHERE c.snt_code='abcd' AND vdc.id=111 AND >> vm.uuid='422a141f-5e46-b0f2-53b8-e31070c883ed' >> ) row > > > The output is 2 rows of JSON data, but I would like to roll up those 2 rows > so that the 2 "virtualinterfaces" are in a nested JSON field called > "interfaces"... The only way I have found to accomplish this so far is to > use a function to grab the joined interface data like: > > SELECT row_to_json(row) AS json > FROM ( > SELECT > c.snt_code AS "snt_code", > vdc.id AS "vdc", > vm.uuid AS "uuid", > vm.name AS "name", > vm.os AS "os", > vm.service_type AS "service_type", > vm.template_name AS "template_name", > (get_vm_with_interfaces(vm.id)) as interfaces > FROM liquorstore_customer c > LEFT JOIN liquorstore_virtualdatacenter vdc ON c.id=vdc.customer_id > LEFT JOIN liquorstore_virtualmachine vm ON > vm.virtual_data_center_id=vdc.id > WHERE c.snt_code='abcd' AND vdc.id=111 AND > vm.uuid='422a141f-5e46-b0f2-53b8-e31070c883ed' > ) row > > > Is there a way to do this in a single join? sure! what is "get_vm_with_interfaces" doing? also, postgres version is extremely relevant here. It's possible to do it in 9.2+, but the solution in 9.4 is very different due to the presence of json_build(). At a very high level, you can aggregate arbitrary records into arrays and those arrays will automatically be converted into json arrays by row_to_json. The three basic mechanisms of making arrays are array_agg(), array(), and array[] -- array[] however is pretty much only useful when dealing with a fixed set of values. For example, here is a query that makes an internal nested array: select row_to_json(q) from ( select v % 3 as k, array_agg(v) from (select generate_series(1,10) v) q group by 1 ) q; merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
