Question 281481 in PostgreSQL Question posted in
The official documentation can be found here.

Getting all the information out of a JSON in PostgreSQL

I have a JSON that looks like this:

"[{""email"": ""AAA"", ""updated_at"": ""AAA"", ""net_income_verified"": AAA}, {""email"": ""BBB"", ""updated_at"": ""BBB"", ""net_income_verified"": BBB}, {""email"": ""CCC"", ""updated_at"": ""CCC"", ""net_income_verified"": CCC}]"

And I would like to get all the data from all the fields so it ends up looking like this:

user_id email updated_at net_income_verified
1 AAA AAA AAA
1 BBB BBB BBB
1 CCC CCC CCC

There are multiple user_ids so the number of entries in the json field could vary.

I tried going one opr one for each user_id, but since the number of entries can vary, I don’t know exactly how many entries I should try.

Edit: Each user_id has a Json, so far my query loks like this:

SELECT user_id, json_field
FROM users
Tags:

2

Answers


  1. 0

    You can use jsonb_array_elements function to extract all entries from the list.

    select j->>'email' email, j->>'updated_at' updated_at, j->>'net_income_verified' net_income_verified
from jsonb_array_elements(
'[
  {"email": "AAA", "updated_at": "AAA", "net_income_verified": 111}, 
  {"email": "BBB", "updated_at": "BBB", "net_income_verified": 222}, 
  {"email": "CCC", "updated_at": "CCC", "net_income_verified": 333}
]'::jsonb) j;
    email updated_at net_income_verified
    AAA AAA 111
    BBB BBB 222
    CCC CCC 333

    However I do not see where user_id attribute could come from.

    Edit

    Given that every record has a JSON field and user_id then the query looks a bit different. I would call this the "old school approach", first flatten (using a lateral join here) and then query. The t CTE is a mimic of the actual table.

    with t (user_id, json_field) as 
(
 values  
 (1, '[{"email": "AAA", "updated_at": "AAA", "net_income_verified": 111},{"email": "BBB", "updated_at": "BBB", "net_income_verified": 222},{"email": "CCC", "updated_at": "CCC", "net_income_verified": 333}]'::jsonb)
)
select user_id, j->>'email' email,
       j->>'updated_at' updated_at, j->>'net_income_verified' net_income_verified
from t cross join lateral jsonb_array_elements(json_field) j;
    user_id email updated_at net_income_verified
    1 AAA AAA 111
    1 BBB BBB 222
    1 CCC CCC 333
    Login or Signup to reply.
  2. 0

    There’s a json_populate_recordset() function for that exact purpose: demo

    create table r1(email text, updated_at text, net_income_verified text);

select user_id, j.* from users, json_populate_recordset(null::r1,json_field) j;
    user_id email updated_at net_income_verified
    1 AAA AAA AAA
    1 BBB BBB BBB
    1 CCC CCC CCC

    You first set up an table/type showing what structure you want your json to be mapped to, then give the function an example record of that table/type along with the target json value. It handles it all for you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search