Re: [GENERAL] 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.phill...@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.
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
Re: [GENERAL] JSON pretty and selecting nested JSON fields
On Mon, Mar 30, 2015 at 12:54 PM, Deven Phillips deven.phill...@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
Re: [GENERAL] 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.kla...@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
Re: [GENERAL] JSON pretty and selecting nested JSON fields
On Mon, Mar 30, 2015 at 3:30 PM, Adrian Klaver
adrian.kla...@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
