[SQL] Getting the queried result in the form of json structure

[msn]

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.




--
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 (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Hector Vass]

cast it

select info::text from sample;

________________________________________
From: pgsql-s...@postgresql.org <pgsql-s...@postgresql.org> on behalf of msn <pionee...@gmail.com>
Sent: 26 April 2016 12:32
To: pgsq...@postgresql.org
Subject: [SQL] Getting the queried result in the form of json structure

[msn]

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

[Adrian Klaver]

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...@aklaver.com

[David G. Johnston]

On Tue, Apr 26, 2016 at 4:32 AM, msn <pionee...@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.

​

 

[Stephen Tahmosh]

If you do a google search using

                postgres json

 

You will get the PostgreSQL Documentation on JSON Functions and Operators

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.

[David G. Johnston]

On Fri, Apr 29, 2016 at 9:20 AM, Stephen Tahmosh <stah...@shieldsrx.com> wrote:

If you do a google search using

                postgres json

 

You will get the PostgreSQL Documentation on JSON Functions and Operators

FWIW I tend to use "RTFM" (in isolation) when I know that it explicitly provides the answer the questioner is asking.​  In this case it does not.

 And, given the example and proper usage of the "->" and "->>" operators​ its safe to assume that the OP already managed to find said documentation.

David J.