Re: Getting the queried result in the form of json structure

On Tue, Apr 26, 2016 at 4:32 AM, msn <pioneer(dot)suri(at)gmail(dot)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.
​