Re: JSON "pretty" and selecting nested JSON fields

On Mon, Mar 30, 2015 at 3:30 PM, Adrian Klaver
<adrian(dot)klaver(at)aklaver(dot)com> wrote:
> On 03/30/2015 10:54 AM, Deven Phillips 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?
>
>
> Well it is documented:
>
> http://www.postgresql.org/docs/9.4/interactive/functions-json.html
>
> row_to_json(record [, pretty_bool]) Returns the row as a JSON object.
> Line feeds will be added between level-1 elements if
> ^^^^^^^
> pretty_bool is true.
>
> I would say post a feature request on --hackers or at ask if work is being
> done on this.

Yeah, also, the OP's problem was made worse by using 'jsonb' inside
the function; jsonb ignores any whitespace formatting (as opposed to
json).

merlin