Re: JSON "pretty" and selecting nested JSON fields

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.

>
> Thanks in advance!
>
> Deven


--
Adrian Klaver
adrian.klaver@aklaver.com