From: | Deven Phillips <deven(dot)phillips(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Muti-table join and roll-up aggregate data into nested JSON? |
Date: | 2015-03-30 11:27:01 |
Message-ID: | CAJw+4NCdPK143z6VweWiL+d7zRRMPRzacEjbQnsTDM0iPzsQqg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Thank in advance!
Deven
From | Date | Subject | |
---|---|---|---|
Next Message | John McKown | 2015-03-30 11:47:01 | Re: How to recover or resent the password for the user 'postgres' |
Previous Message | Felipe Santos | 2015-03-30 11:09:36 | Re: [SQL] [GENERAL] JSON TO POSTGRE TABLE |