[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] Muti-table join and roll-up aggregate data into nested JSON?
From: Deven Phillips <deven.phillips () gmail ! com>
Date: 2015-03-31 14:26:56
Message-ID: CAJw+4NAy5+Bw33wbJp3Mp87BtZ+dTDCLWvMRaG1R7keDyDcxAA () mail ! gmail ! com
[Download RAW message or body]
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.phillips@gmail.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@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
>>
>
>
[Attachment #3 (text/html)]
<div dir="ltr"><div><div><div>OK, I figured out this part and came up \
with:<br><br>SELECT <br> row.snt_code AS "snt_code", <br> \
row.vdc AS "vdc",<br> row.uuid AS "uuid", <br> \
row_to_json(row, true)::json AS "json"<br>FROM (<br> SELECT <br> \
vm.*,<br> array_agg(vi),<br> CONCAT('<a \
href="https://mysite.mydomain.tld/v3/customer/">https://mysite.mydomain.tld/v3/customer/</a>', \
vm.snt_code, '/vdc/', vm.vdc, '/vm/', vm.uuid) AS \
"self",<br> 'cc.v3.vm' AS "type"<br> \
FROM virtual_machines vm<br> LEFT JOIN virtual_interfaces vi ON \
vm.vmid=vi.vmid<br> GROUP BY vm.snt_code, vm.vdc, vm.vmid, vm.uuid, <a \
href="http://vm.name">vm.name</a>, vm.os, vm.service_type, vm.template_name<br>) \
row;<br><br></div>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?<br><br></div>Thanks again for the help!!<br><br></div>Deven<br></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Mon, Mar 30, 2015 at 10:25 PM, \
Deven Phillips <span dir="ltr"><<a href="mailto:deven.phillips@gmail.com" \
target="_blank">deven.phillips@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div><div><div><div>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:<br><br>SELECT <br> row.snt_code AS \
"snt_code", <br> row.vdc AS "vdc",<br> row.uuid AS \
"uuid", <br> row_to_json(row, true)::json AS "json"<br>FROM \
(<br> SELECT <br> vm.*,<br> CONCAT('<a \
href="https://int.cloudcontrol.sgns.net/v3/customer/" \
target="_blank">https://int.cloudcontrol.sgns.net/v3/customer/</a>', vm.snt_code, \
'/vdc/', vm.vdc, '/vm/', vm.uuid) AS "self",<br> \
'cc.v3.sungardas.vm' AS "type",<br> (SELECT DISTINCT * \
FROM virtual_interfaces WHERE vmid=<span style="color:rgb(255,0,0)"><b><a \
href="http://vm.id" target="_blank">vm.id</a></b></span>) as interfaces<br> \
FROM virtual_machines vm<br>) row;<br><br></div>Placing the <a href="http://vm.id" \
target="_blank">vm.id</a> value there for the WHERE clause gives the \
error:<br><br>SQL Error [42703]: ERROR: column <a href="http://vm.id" \
target="_blank">vm.id</a> does not exist<br> Position: 351<br> ERROR: column <a \
href="http://vm.id" target="_blank">vm.id</a> does not exist<br> Position: \
351<br><br></div>Is there some way to make that value available to the inner \
select?<br><br></div>Thanks in advance!<span class="HOEnZb"><font \
color="#888888"><br><br></font></span></div><span class="HOEnZb"><font \
color="#888888">Deven<br></font></span></div><div class="HOEnZb"><div class="h5"><div \
class="gmail_extra"><br><div class="gmail_quote">On Mon, Mar 30, 2015 at 5:46 PM, \
Merlin Moncure <span dir="ltr"><<a href="mailto:mmoncure@gmail.com" \
target="_blank">mmoncure@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><span>On Mon, Mar 30, 2015 at 4:22 PM, Deven Phillips<br> \
<<a href="mailto:deven.phillips@gmail.com" \
target="_blank">deven.phillips@gmail.com</a>> wrote:<br> > I'm using \
PostgreSQL 9.4.1 on Ubuntu 14.10.<br> ><br>
> The function does the following:<br>
><br>
> DROP FUNCTION get_vm_with_interfaces(vm_id BIGINT);<br>
><br>
> CREATE OR REPLACE FUNCTION get_virtual_interfaces(vm_id BIGINT) RETURNS<br>
> jsonb AS $$<br>
> DECLARE<br>
> res jsonb;<br>
> BEGIN<br>
> SELECT array_to_json(array_agg(row_to_json(i, true)), true)<br>
> FROM (<br>
> SELECT DISTINCT * FROM virtual_interfaces WHERE vmid=vm_id) \
i<br> > INTO res;<br>
> RETURN res;<br>
> END;<br>
> $$ LANGUAGE PLPGSQL;<br>
<br>
</span>please, try to refrain from top posting. particularly with emails<br>
like this where the context of the question is important. Anyways,<br>
your inner function could be trivially inlined as so:<br>
<span><br>
SELECT row_to_json(row) AS json<br>
FROM (<br>
SELECT<br>
c.snt_code AS "snt_code",<br>
<a href="http://vdc.id" target="_blank">vdc.id</a> AS \
"vdc",<br> vm.uuid AS "uuid",<br>
<a href="http://vm.name" target="_blank">vm.name</a> AS \
"name",<br> vm.os AS "os",<br>
vm.service_type AS "service_type",<br>
vm.template_name AS "template_name",<br>
</span> ( -- get_vm_with_interfaces(<a href="http://vm.id" \
target="_blank">vm.id</a>)<br> <span> SELECT \
array_to_json(array_agg(row_to_json(i, true)), true)<br> FROM (<br>
</span> SELECT DISTINCT * FROM virtual_interfaces vi WHERE \
vmid=vm_id<br> ) i<br>
<span> ) as interfaces<br>
FROM liquorstore_customer c<br>
LEFT JOIN liquorstore_virtualdatacenter vdc ON <a href="http://c.id" \
target="_blank">c.id</a>=vdc.customer_id<br>
LEFT JOIN liquorstore_virtualmachine vm ON vm.virtual_data_center_id=<a \
href="http://vdc.id" target="_blank">vdc.id</a><br>
WHERE c.snt_code='abcd' AND <a href="http://vdc.id" \
target="_blank">vdc.id</a>=111 AND<br> \
vm.uuid='422a141f-5e46-b0f2-53b8-e31070c883ed'<br> ) row<br>
<br>
</span>I would personally simplify the subquery portion to:<br>
( -- get_vm_with_interfaces(<a href="http://vm.id" \
target="_blank">vm.id</a>)<br> SELECT array_agg(i)<br>
FROM (<br>
SELECT DISTINCT * FROM virtual_interfaces vi WHERE vmid=vm_id<br>
) i<br>
<br>
, allowing for the outer 'to_json' to handle the final<br>
transformation. I'm not going to do it for you, but you could<br>
probably simplify the query even further by moving the aggregation out<br>
of a correlated subquery and into the basic field list, which would be<br>
faster for certain distributions of data.<br>
<br>
Also, a note about jsonb, which you used inside the inner function.<br>
jsonb is much better than type 'json' for any case involving<br>
manipulation of the json, searching, or repeated sub-document<br>
extraction. However, for serialization to an application, it is<br>
basically pessimal as it involves building up internal structures that<br>
the vanilla json type does not involve. The basic rule of thumb is:<br>
serialization, json, everything else, jsonb.<br>
<span><font color="#888888"><br>
merlin<br>
</font></span></blockquote></div><br></div>
</div></div></blockquote></div><br></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic