Looping through JSON and insert data to Postgres DB

Question

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
     }
   ]
 }

Answer 1

You need to combine two jsonb_array_elements() calls:

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

Online example

Answer 2

Suppose that you have 2 tables;

create table class_table (
  id integer,
  class_name text 
);

create table json_table(
  id serial primary key,
  data jsonb not null
);

Here is the query;

select 
  (jsonb_populate_record(
        null::class_table, 
      jsonb_path_query(t.data, '$.suites[*].cases[*]')
    )).*
from json_table t

• (..).* Part converts record into fields
• json(b)_populate_record expands the object in json(b) to a row whose columns match the record type (class_table)
• And finally json(b)_path_query gets all items returned by the path ('$.suites[*].cases[*]')

expected result:

| id  | class_name |
| --- | ---------- |
| 231 | testname1  |
| 233 | testname2  |