If you do a google search using
postgres json
You will get the PostgreSQL Documentation on JSON Functions and Operators
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of David G. Johnston
Sent: Friday, April 29, 2016 12:16 PM
To: msn <pioneer.suri@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Getting the queried result in the form of json structure
I am using postgresql for my webapplication. I am new to this
Postgresql-json. I Just want to get the select query result in the form of
json structure.
Here are my details:
Create table
============
- create table sample(id, serial, info jsonb);
Insert query
============
- insert into sample("info") values('{"person": {"phone":
9804484234,"name":{"firstname":"Alice", "lastname":"bob"}, "empId": "E067",
"age":25}');
select query:
=============
select "info"->'person'->>'lastname' from sample;
result: bob
but I want to get the above result along with the json nodes like below:
result: {"person":
{"name":
{"lastname":"bob"}
}
}
could any tell me how to get my expected result structure from database.
I'm reasonably certain this is not provided for by PostgreSQL. The lack of any affirmative response would tend to support my conclusion.
I imagine you can write a function that accomplishes this goal by obtaining the found value as each element level, as well as keeping track of depth, and constructs a json value itself from the results.
THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY CONTAIN INFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the intended recipient, your use of this message for any purpose is strictly prohibited. If you have received this communication in error, please delete the message without making any copies and notify the sender so that we may correct our records. Thank you.