Looping through JSON and insert data to Postgres DB Below is how i have my data at the moment. How would i achieve in postgres with Postgresql to get all those "class_name" and "id" values and store them on their own rows in the database. I can store the data as JSON or JSONB, that doesnt matter but im having trouble looping through all the cases and getting all the values.

I have tried json_each and combination of jsonb_query_path and json_array_elements but only have managed to get only the first case in each suite.

Would appreciate any help here and i would prefer to do this with postgresql instead of python

 {
  "duration" : 6109.1104,
  "failCount" : 0,
  "passCount" : 4389,
  "skipCount" : 0,
  "suites" : [
    {
    "cases" : [
      {
        "class_name": "testname1",
        "id": 231
      },
      {
        "class_name": "testname2",
         "id": 233
      }
    ],
    "duration" : 0.012,
    "id" : null,
    "name" : "EventTest",
    "stderr" : null,
    "stdout" : null,
    "timestamp" : null
     }
   ]
 }
You need to combine two jsonb_array_elements() calls:

select c.cases -&gt;&gt; 'id' as id,
       c.cases -&gt;&gt; 'class_name' as class_name
from the_table
  cross join jsonb_array_elements(the_column -&gt; 'suites') as s(suite)
  cross join jsonb_array_elements(s.suite -&gt; 'cases') as c(cases);


Online example