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

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
>