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

Reply via email to