Re: JSON "pretty" and selecting nested JSON fields

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(dot)phillips(at)gmail(dot)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
>