Re: Muti-table join and roll-up aggregate data into nested JSON? - Mailing list pgsql-general

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