Here's an example of the JSON output I am getting: {"customer_code":"abcd", "vdc":1241, "vmid":114778, "uuid":"421ea391-b292-ca2e-9a3a-6da3037748c8", "name":"vmname", "os":"Red Hat Enterprise Linux 6 (64-bit)", "service_type":"CU", "template_name":"", "self":" https://mysite.mydomain.tld/v3/customer/3mcc/vdc/1241/vm/421ea391-b292-ca2e-9a3a-6da3037748c8 ", "type":"cc.v3.sungardas.vm", "interfaces":[{"vlan": null, "vmid": 114778, "order": 1, "ip_address": "10.129.114.45", "is_backend": true, "is_gateway": false, "is_reserved": false, "mac_address": "00:50:56:9e:25:40"}, {"vlan": null, "vmid": 114778, "order": 0, "ip_address": "10.137.154.212", "is_backend": true, "is_gateway": false, "is_reserved": false, "mac_address": "00:50:56:9e:25:3d"}]}
I would expect it to be: {"customer_code":"abcd", "vdc":1241, "vmid":114778, "uuid":"421ea391-b292-ca2e-9a3a-6da3037748c8", "name":"vmname", "os":"Red Hat Enterprise Linux 6 (64-bit)", "service_type":"CU", "template_name":"", "self":"https://mysite.mydomain.tld /v3/customer/3mcc/vdc/1241/vm/421ea391-b292-ca2e-9a3a-6da3037748c8", "type":"cc.v3.vm", "interfaces":[ {"vlan": null, "vmid": 114778, "order": 1, "ip_address": "10.129.114.45", "is_backend": true, "is_gateway": false, "is_reserved": false, "mac_address": "00:50:56:9e:25:40" }, {"vlan": null, "vmid": 114778, "order": 0, "ip_address": "10.137.154.212", "is_backend": true, "is_gateway": false, "is_reserved": false, "mac_address": "00:50:56:9e:25:3d"}]} On Mon, Mar 30, 2015 at 1:54 PM, Deven Phillips <deven.phill...@gmail.com> wrote: > Hi all, > > I have a query which selects several rows of data, and contained in > one of those rows is some aggregated JSON data. I am using row_to_json() to > make the whole output JSON and I am providing "true" for pretty formatting > of the JSON. The problem that I am seeing is that they nested JSON block is > not being prettified along with the outer JSON. > > Example: > > I have a function which takes a single key param and returns a JSON array: > > 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; > > That function is then used in another query to provide a nested JSON > containing the array: > > SELECT > row.snt_code AS "snt_code", > row.vdc AS "vdc", > row.uuid AS "uuid", > row_to_json(row, true) 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", > (get_virtual_interfaces(vm.vmid)) as interfaces > FROM virtual_machines vm > ) row; > > The outer level of JSON is "pretty printed", but the content of the array > from the function is NOT, even though I have specified that it should be. > Any suggestions of how to address this? > > Thanks in advance! > > Deven >