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 ->> '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

  • This seems like a solution that I'd like to use but it results me with error: "more than one row returned by a subquery used as an expression" when i want to insert the data into column on my table – Lauri Huhta Mar 2 '20 at 6:07
  • @LauriHuhta: then you are not using the query in my answer. – a_horse_with_no_name Mar 2 '20 at 6:33
  • Here is my query: INSERT INTO {TEST_RESULT_TABLE}( class_name ) SELECT( c.cases ->> 'className' AS class_name ) FROM {staging_t} CROSS JOIN jsonb_array_elements(data -> 'suites') AS s(suite) CROSS JOIN jsonb_array_elements(s.suite -> 'cases') AS c(cases) ON CONFLICT DO NOTHING – Lauri Huhta Mar 2 '20 at 6:42
  • Remove the parentheses around the column expression ( c.cases ->> 'className' AS class_name ) should be c.cases ->> 'className' AS class_name – a_horse_with_no_name Mar 2 '20 at 6:46
  • Ah such a foolish mistake. Thank you so much! You were a huge help and answered fast! – Lauri Huhta Mar 2 '20 at 6:49

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;

      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  |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.