Thread: 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 ============ -
insertinto 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.
--
View this message in context:
http://postgresql.nabble.com/Getting-the-queried-result-in-the-form-of-json-structure-tp5900554.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
cast it
select info::text from sample;
________________________________________
From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of msn <pioneer.suri@gmail.com>
Sent: 26 April 2016 12:32
To: pgsql-sql@postgresql.org
Subject: [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 ============ -
insertinto 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.
--
View this message in context:
http://postgresql.nabble.com/Getting-the-queried-result-in-the-form-of-json-structure-tp5900554.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Could any body please tell me, Is my requirement possible? It would be appreciated if i get the response, because It is completely blocking my task. -- View this message in context: http://postgresql.nabble.com/Getting-the-queried-result-in-the-form-of-json-structure-tp5900554p5901157.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On 04/29/2016 02:40 AM, msn wrote:
> Could any body please tell me, Is my requirement possible? It would be
> appreciated if i get the response, because It is completely blocking my
> task.
Did you try Hectors suggestion:
"cast it
select info::text from sample;"
test=# select info::text from sample; info
------------------------------------------------------------------------------------------------------------------
{"person":{"age": 25, "name": {"lastname": "bob", "firstname":
"Alice"}, "empId": "E067", "phone": 9804484234}}
If that is not what you want there are a whole slew of functions that
allow you to manipulate the data:
http://www.postgresql.org/docs/9.5/interactive/functions-json.html
*NOTE* the above is from Postgres 9.5, not all of them may be available
in earlier version.
>
>
>
> --
> View this message in context:
http://postgresql.nabble.com/Getting-the-queried-result-in-the-form-of-json-structure-tp5900554p5901157.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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.
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
On Tue, Apr 26, 2016 at 4:32 AM, msn <pioneer.suri@gmail.com> wrote:
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.
David J.
If you do a google search using
postgres json
You will get the PostgreSQL Documentation on JSON Functions and Operators