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

Sorry, typo!!

CREATE OR REPLACE FUNCTION get_vm_with_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;

On Mon, Mar 30, 2015 at 5:22 PM, Deven Phillips <deven(dot)phillips(at)gmail(dot)com>
wrote:

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