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

OK, I figured out this part and came up with:

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.*,
array_agg(vi),
CONCAT('https://mysite.mydomain.tld/v3/customer/', vm.snt_code,
'/vdc/', vm.vdc, '/vm/', vm.uuid) AS "self",
'cc.v3.vm' AS "type"
FROM virtual_machines vm
LEFT JOIN virtual_interfaces vi ON vm.vmid=vi.vmid
GROUP BY vm.snt_code, vm.vdc, vm.vmid, vm.uuid, vm.name, vm.os,
vm.service_type, vm.template_name
) row;

Now, the next step is that "virtual_interfaces" and "virtual_machines" are
actually views I defined. I would like to break those out into joined
tables and still aggregate the data into an array. The problem I am having
is that I cannot put the results of multiple tables into a single
array_add() call. How can I aggregate multiple joined tables into a single
array?

Thanks again for the help!!

Deven

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

> 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
> <http://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(at)gmail(dot)com>
> wrote:
>
>> On Mon, Mar 30, 2015 at 4: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;
>>
>> 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
>>
>
>