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? Thank in advance! Deven