Extracting values from JSONB in Postgresql

Question

I have a table (users) in which I store different columns, one of them being a jsonb column named content. The other columns don't matter because they refer to date and other non-related stuff. In that jsonb column we store a file that has the following syntax:

{
 "Root": {
    "Users": {
        "user1": {
            "Email": {
                "_value": "[email protected]"
            },
            "FullName": {
                "_value": "User1"
            },
            "Teams": {
                "_value": "TeamA, TeamB"
            },
        "user2": {
            "Email": {
                "_value": "[email protected]"
            },
            "FullName": {
                "_value": "User2"
            },
            "Teams": {
                "_value": "TeamA, TeamB, TeamC"
            }, 
....

What I am trying to achieve: extract all users from this jsonb and each parameter in a table that should look like this:

username |      email    |  fullname   |   teams
user1    |[email protected]|  User1      | TeamA, TeamB
user2    |[email protected]|  User2      | TeamA, TeamB, TeamC

I tried using jsonb_object_keys(content->'Root'->'users') and managed to extract all the users but can't seem to go forward in the tree to extract each value for each user. I am blocked and can't seem to find anything that suits me. The final goal would be extracting every user with said details based on a Team parameter I provide, so I will be putting everything in a function. I inherited this structure and in the same time I am a newbie in using jsonb general. Even something that can flatten in some way this jsonb would be great.

PostgreSQL version used: 9.5

If anyone has some input it would be greatly appreciated. Thanks.

Vao Tsun · Accepted Answer · 2017-02-21 15:45:00Z

2

teams is a homework, the rest is sampled below:

t=# with o as (with j as (select '
{
 "Root": {
    "Users": {
        "user1": {
            "Email": {
                "_value": "[email protected]"
            },
            "FullName": {
                "_value": "User1"
            },
            "Teams": {
                "_value": "TeamA, TeamB"
            }
        },
        "user2": {
            "Email": {
                "_value": "[email protected]"
            },
            "FullName": {
                "_value": "User2"
            },
            "Teams": {
                "_value": "TeamA, TeamB, TeamC"
            }
        }
    }
  }
}'::jsonb v)
select key,value from j join jsonb_each(j.v->'Root'->'Users') on true) select key username, value->'Email'->>'_value' email, value->'FullName'->>'_value' fullname from o;
 username |      email      | fullname
----------+-----------------+----------
 user1    | [email protected] | User1
 user2    | [email protected] | User2
(2 rows)

answered Feb 21, 2017 at 15:45

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

2 Comments

MisterM Over a year ago

Thanks for help. Will test tomorrow and then will accept as answer. One question only, what do you mean by teams is a homework? I don't understand. Thanks

Vao Tsun Over a year ago

I did not include last column - leaving it for you to do

Collectives™ on Stack Overflow

Extracting values from JSONB in Postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related