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?
