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; Thanks for any help! Deven On Mon, Mar 30, 2015 at 3:25 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Mon, Mar 30, 2015 at 6:27 AM, Deven Phillips > <deven.phill...@gmail.com> wrote: > > 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? > > sure! what is "get_vm_with_interfaces" doing? > > also, postgres version is extremely relevant here. It's possible to do > it in 9.2+, but the solution in 9.4 is very different due to the > presence of json_build(). > > At a very high level, you can aggregate arbitrary records into arrays > and those arrays will automatically be converted into json arrays by > row_to_json. The three basic mechanisms of making arrays are > array_agg(), array(), and array[] -- array[] however is pretty much > only useful when dealing with a fixed set of values. > > For example, here is a query that makes an internal nested array: > > select row_to_json(q) > from > ( > select v % 3 as k, array_agg(v) > from (select generate_series(1,10) v) q group by 1 > ) q; > > > merlin >