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

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

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@gmail.com> wrote:

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

On Mon, Mar 30, 2015 at 4:22 PM, Deven Phillips
<deven.phillips@gmail.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;

please, try to refrain from top posting.  particularly with emails
like this where the context of the question is important.  Anyways,
your inner function could be trivially inlined as so:

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)
          SELECT array_to_json(array_agg(row_to_json(i, true)), true)
          FROM (
            SELECT DISTINCT * FROM virtual_interfaces vi WHERE vmid=vm_id
        ) i
      ) 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

I would personally simplify the subquery portion to:
        ( -- get_vm_with_interfaces(vm.id)
          SELECT array_agg(i)
          FROM (
            SELECT DISTINCT * FROM virtual_interfaces vi WHERE vmid=vm_id
        ) i

, allowing for the outer 'to_json' to  handle the final
transformation.  I'm not going to do it for you, but you could
probably simplify the query even further by moving the aggregation out
of a correlated subquery and into the basic field list, which would be
faster for certain distributions of data.

Also, a note about jsonb, which you used inside the inner function.
jsonb is much better than type 'json' for any case involving
manipulation of the json, searching, or repeated sub-document
extraction.  However, for serialization to an application, it is
basically pessimal as it involves building up internal structures that
the vanilla json type does not involve. The basic rule of thumb is:
serialization, json, everything else, jsonb.

merlin

I have already attempted a similar approach and I could not find a way to pass the outer value of the VM ID to the inner SELECT. For example:

SELECT
    row.snt_code AS "snt_code",
    row.vdc AS "vdc",
    row.uuid AS "uuid",
    row_to_json(row, true)::json AS "json"
FROM (
    SELECT
        vm.*,
        CONCAT('https://int.cloudcontrol.sgns.net/v3/customer/', vm.snt_code, '/vdc/', vm.vdc, '/vm/', vm.uuid) AS "self",
        'cc.v3.sungardas.vm' AS "type",
        (SELECT DISTINCT * FROM virtual_interfaces WHERE vmid=vm.id) as interfaces
    FROM virtual_machines vm
) row;

Placing the vm.id value there for the WHERE clause gives the error:

SQL Error [42703]: ERROR: column vm.id does not exist
  Position: 351
  ERROR: column vm.id does not exist
  Position: 351

Is there some way to make that value available to the inner select?

Thanks in advance!

Deven

On Mon, Mar 30, 2015 at 5:46 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Mar 30, 2015 at 4:22 PM, Deven Phillips
<deven.phillips@gmail.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;

please, try to refrain from top posting.  particularly with emails
like this where the context of the question is important.  Anyways,
your inner function could be trivially inlined as so:

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)
          SELECT array_to_json(array_agg(row_to_json(i, true)), true)
          FROM (
            SELECT DISTINCT * FROM virtual_interfaces vi WHERE vmid=vm_id
        ) i
      ) 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

I would personally simplify the subquery portion to:
        ( -- get_vm_with_interfaces(vm.id)
          SELECT array_agg(i)
          FROM (
            SELECT DISTINCT * FROM virtual_interfaces vi WHERE vmid=vm_id
        ) i

, allowing for the outer 'to_json' to  handle the final
transformation.  I'm not going to do it for you, but you could
probably simplify the query even further by moving the aggregation out
of a correlated subquery and into the basic field list, which would be
faster for certain distributions of data.

Also, a note about jsonb, which you used inside the inner function.
jsonb is much better than type 'json' for any case involving
manipulation of the json, searching, or repeated sub-document
extraction.  However, for serialization to an application, it is
basically pessimal as it involves building up internal structures that
the vanilla json type does not involve. The basic rule of thumb is:
serialization, json, everything else, jsonb.

merlin