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

[Deven Phillips <deven.phillips@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

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