How to insert array of nested json in a table in Postgres

Question

We have a user data that is fetched from an api(a third party) and from that data we need to store the data into a table using postgresql.

I am passing in json data from an API to the Postgres function tests. The data represents new users of my application and contains the data I want to insert into the users table (if the record does not already exist)

Below is my code.

with recursive jsondata AS(
    SELECT 
        data ->0->>'rmid' as rmid,
        data ->0->>'camid' as camid,
        data ->0->'userlist' as children 
    FROM (
        SELECT '[{"rmid":1,"camid":2,"userlist":[{"userid":34,"username":33},{"userid":35,"username":36}]},{"rmid":3,"camid":4,"userlist":[{"userid":44,"username":43},{"userid":44,"username":43}]}]'::jsonb as data
    ) s
    
    UNION 
    
    SELECT
        value ->0->> 'rmid',
        value ->0->> 'camid',
        value ->0-> 'children'
    FROM jsondata,jsonb_array_elements(jsondata.children)
    )SELECT 
    rmid,camid,
    jsonb_array_elements(children) ->> 'userid' as userid,
    jsonb_array_elements(children) ->> 'username' as username
FROM jsondata WHERE children IS NOT NULL

The output I am getting

The output I expect

I need the object of the array at all the index in table, How do I achieve that?I Please help

It is not very clear what exactly is the problem. You have a table with JSONB column and you store some values in this column and then you fetch/select those values back. I don't see anything wrong here. — IVO GELOV
– IVO GELOV, Commented Sep 1, 2022 at 9:03
I need the object at index 2 of the array to get inserted in the table too. — Nitin Kumar
– Nitin Kumar, Commented Sep 1, 2022 at 9:05
Aren't you already inserting the whole array with all of its indexes? — IVO GELOV
– IVO GELOV, Commented Sep 1, 2022 at 9:08
data ->0->>'rmid' as rmid, data ->0->>'camid' as camid, data ->0->'userlist' as children, I am inserting only the 0th index, How do I access all the index — Nitin Kumar
– Nitin Kumar, Commented Sep 1, 2022 at 9:14
Just like you do with the 0th index - simply replace 0 with 2. Or, just store the whole JSON from the API. — IVO GELOV
– IVO GELOV, Commented Sep 1, 2022 at 9:20

qaziqarta · Accepted Answer · 2022-09-01 18:24:55Z

2

Actually you don't need recursive query here, json processing functions are enough to achieve your desired output:

create table test(
  rmid int,
  camid int,
  userid int,
  username text
);

insert into test
select 
  (a->>'rmid')::int rmid,
  (a->>'camid')::int camid,
  (b->>'userid')::int userid,
  b->>'username' username
from (
select '[{"rmid":1,"camid":2,"userlist":[{"userid":34,"username":33},{"userid":35,"username":36}]},{"rmid":3,"camid":4,"userlist":[{"userid":44,"username":43},{"userid":44,"username":43}]}]'::jsonb as data
) j 
cross join lateral jsonb_array_elements(j.data) a
cross join lateral jsonb_array_elements(a->'userlist') b
;

select * from test;

rmid | camid | userid | username
---: | ----: | -----: | :-------
   1 |     2 |     34 | 33      
   1 |     2 |     35 | 36      
   3 |     4 |     44 | 43      
   3 |     4 |     44 | 43

db<>fiddle here

edited Sep 1, 2022 at 18:24

answered Sep 1, 2022 at 10:50

qaziqarta

1,9541 gold badge6 silver badges13 bronze badges

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

2 Comments

Nitin Kumar Over a year ago

What should I do to insert this into a table?

qaziqarta Over a year ago

@NitinKumar, see the edit with the example of INSERT. I don't know what your table look like, but assuming that ids are integers you would need some casting (::int).

Collectives™ on Stack Overflow

How to insert array of nested json in a table in Postgres

Below is my code.

The output I am getting

The output I expect

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Below is my code.

The output I am getting

The output I expect

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related