Postgres: select data to jsonb object

Question

I'm new to json in postgres, so please don't throw anything at me. I'm trying to select data to jsonb object. I plan to use it for update from select. Right now it looks like this:

select to_jsonb(full_address) as full_address
from (
         select "user".living_addr      as living_id,
                "user".living_addr_path as living_path,
                null                    as living_house,

                "user".address_level_id as registration_id,
                "user".address_path     as registration_path,
                null                    as registration_house,

                "user".work_addr      as work_id,
                "user".work_addr_path as work_path,
                null                  as work_house,

                public."user".user_id

         from public."user"
     ) full_address
group by user_id, full_address
order by user_id

But I need also "living_id", "living_path", "living_house" to be into the object like this:

{
    "living": {"id", "path", "house"}
}

and same for registration, work and learn.

Also, I don't need user_id in result object.

Tried to use subqueries like:

select to_jsonb(living)            as living,
       to_jsonb(registration)      as registration,
       ...
from (
    select "user".living_addr      as id,
           "user".living_addr_path as path,
           null                    as house
    from public."user"
) living,
(
    select "user".address_level_id as id,
           "user".address_path     as path,
           null                    as house
    from public."user"
) registration
... etc

But, right now the result is wrong without grouping, and I can't find a clue how to use group by in this. There should be another simpler way I don't see.

The result object should look like this:

{
    "work": {"id": ..., "path": [..., ...], "house": null}, 
    "living": {"id": ..., "path": [..., ...], "house": null}, 
    "registration": {"id": ..., "path": [..., ...], "house": null}
}

Is there a way to do this in postgres 9.6?

Hmm, now I notice then there is more useful operators, then I first thought: postgresql.org/docs/9.6/functions-json.html — Konstantin Tushov
– Konstantin Tushov, Commented May 20, 2019 at 9:46

Konstantin Tushov · Accepted Answer · 2019-05-20 11:20:34Z

2

Yep, it's much more simple:

select jsonb_build_object(
               'registration', jsonb_build_object('id', u.address_level_id, 'path', u.address_path, 'house', null),
               'living', jsonb_build_object('id', u.living_addr, 'path', living_addr_path, 'house', null),
               'work', jsonb_build_object('id', u.work_addr, 'path', u.work_addr_path, 'house', null)
           )
from public.user u

And no update from select needed, just update.

edited May 20, 2019 at 11:20

answered May 20, 2019 at 11:13

Konstantin Tushov

331 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: select data to jsonb object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related