JSON "pretty" and selecting nested JSON fields

Started by Deven Phillipsabout 11 years ago5 messagesgeneral

deven.phillips@gmail.com

about 11 years ago

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

Deven Phillips

deven.phillips@gmail.com

about 11 years ago

In reply to: Deven Phillips (#1)

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.phillips@gmail.com>
wrote:

Show quoted text

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

Merlin Moncure

mmoncure@gmail.com

about 11 years ago

In reply to: Deven Phillips (#1)

Re: JSON "pretty" and selecting nested JSON fields

On Mon, Mar 30, 2015 at 12:54 PM, Deven Phillips
<deven.phillips@gmail.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.

It looks like a bug. The 'outer' to_json's pretty print feature should
control the whole structure IMO. Personally, I think you'll have
better luck rigging another function to do whitespace insertion
formatting.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

about 11 years ago

In reply to: Deven Phillips (#1)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Merlin Moncure

mmoncure@gmail.com

about 11 years ago

In reply to: Adrian Klaver (#4)

Re: JSON "pretty" and selecting nested JSON fields

On Mon, Mar 30, 2015 at 3:30 PM, Adrian Klaver
<adrian.klaver@aklaver.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general