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

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

CodePudding user response：

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