Re: Muti-table join and roll-up aggregate data into nested JSON?

I'm using PostgreSQL 9.4.1 on Ubuntu 14.10.

The function does the following:

DROP FUNCTION get_vm_with_interfaces(vm_id BIGINT);

CREATE OR REPLACE FUNCTION get_virtual_interfaces(vm_id BIGINT) RETURNS
jsonb AS $$
DECLARE
res jsonb;
BEGIN
SELECT array_to_json(array_agg(row_to_json(i, true)), true)
FROM (
SELECT DISTINCT * FROM virtual_interfaces WHERE vmid=vm_id) i
INTO res;
RETURN res;
END;
$$ LANGUAGE PLPGSQL;

Thanks for any help!

Deven

On Mon, Mar 30, 2015 at 3:25 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Mon, Mar 30, 2015 at 6:27 AM, Deven Phillips
> <deven(dot)phillips(at)gmail(dot)com> 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
>