From: | Deven Phillips <deven(dot)phillips(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | JSON "pretty" and selecting nested JSON fields |
Date: | 2015-03-30 17:54:54 |
Message-ID: | CAJw+4NBBE1Ae+xBxbOiKiTr3Z-WeU=zX5BBuqg9jGRTG_=bTTQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Deven Phillips | 2015-03-30 18:00:51 | Re: JSON "pretty" and selecting nested JSON fields |
Previous Message | Vincent Veyron | 2015-03-30 17:51:42 | Re: Re: [GENERAL] Link Office Word form document with data from PostgreSQL |