Re: JSON "pretty" and selecting nested JSON fields

[Deven Phillips <deven.phillips@xxxxxxxxx>]

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.phillips@xxxxxxxxx> 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