Parsing this complicate JSON

[simonmng]

I need the help of the Parsing-Champions. My JSON looks like this when I use the API, it is structured in “Teststeps” and “TeststepFolders”. The teststeps are always above the belonging teststepFolder.
The example JSON looks like this:

[{
“teststeps” : [ {
“name” : “b”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “‘xyz’”,
“dType” : “TestStep”
} ],
“name” : “a”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
}, {
“teststeps” : [ {
“name” : “d”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “e”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“teststeps” : [ {
“name” : “g”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
} ],
“name” : “f”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
} ],
“name” : “c”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
}, {
“teststeps” : [ {
“name” : “i”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “j”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “k”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“teststeps” : [ {
“name” : “m”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “n”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“teststeps” : [ {
“name” : “p”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
} ],
“name” : “o”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
} ],
“name” : “l”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
}, {
“teststeps” : [ {
“name” : “r”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
} ],
“name” : “q”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
}, {
“teststeps” : [ {
“teststeps” : [ {
“name” : “u”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
} ],
“name” : “t”,
“description” : “null”,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
} ],
“name” : “s”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
} ],
“name” : “h”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
}, {
“teststeps” : [ {
“teststeps” : [ {
“name” : “x”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
} ],
“name” : “w”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
}, {
“name” : “z”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “z”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “aa”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “ab”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
} ],
“name” : “v”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
}, {
“teststeps” : [ {
“name” : “ad”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “ae”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “af”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
} ],
“name” : “ac”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStepFolder”
}, {
“name” : “ag”,
“description” : null,
“verdict” : “PASSED”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}, {
“name” : “ah”,
“description” : null,
“verdict” : “NONE”,
“expectedResult” : “xyz”,
“dType” : “TestStep”
}]

and I need the result to look like this:

I am thankful for any help

[JPollet]

Hi,
Seemingly, this is not a json format. Perhaps you need to copy/paste the text in the forum editor with the “</>” option or attach the json file.

edit : I manage to convert in json (the problem is the double quotes).

[simonmng]

But it is JSON format, i can parse it in the JSON Path node i just dont get to the wanted result.

[takbb]

Hi @simonmng , as @JPollet has mentioned, please re-upload your json using the </> button on the forum message toolbar. i.e Paste in the text, then highlight it and press the following button:

This allows you to upload the json without it being “converted” by the forum software. Currently you will see that what you have uploaded contains “smart quotes” whereas json requires these to be normal double quotes

e.g. you have uploaded:

[{
“teststeps” : [ {
“name” : “b”,

whereas in correct json this would be

[{
"teststeps" : [ {
"name" : "b",

(notice that the double-quotes are different).

You can test this for yourself by attempting to convert the text as it appears in the above message, into JSON using KNIME. If you copy/paste it, it will currently result in this error:

As it is, anybody wishing to help you would have to correct your json first, and there is no way we can know if other changes have not crept in too.

thanks

[simonmng]

[{
  "teststeps" : [ {
    "name" : "b",
    "description" : null,
    "verdict" : "NONE",
    "expectedResult" : "'xyz'",
    "dType" : "TestStep"
  } ],
  "name" : "a",
  "description" : null,
  "verdict" : "NONE",
  "expectedResult" : "xyz",
  "dType" : "TestStepFolder"
}, {
  "teststeps" : [ {
    "name" : "d",
    "description" : null,
    "verdict" : "NONE",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "name" : "e",
    "description" : null,
    "verdict" : "NONE",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "teststeps" : [ {
      "name" : "g",
      "description" : null,
      "verdict" : "NONE",
      "expectedResult" : "xyz",
      "dType" : "TestStep"
    } ],
    "name" : "f",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStepFolder"
  } ],
  "name" : "c",
  "description" : null,
  "verdict" : "PASSED",
  "expectedResult" : "xyz",
  "dType" : "TestStepFolder"
}, {
  "teststeps" : [ {
    "name" : "i",
    "description" : null,
    "verdict" : "NONE",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "name" : "j",
    "description" : null,
    "verdict" : "NONE",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "name" : "k",
    "description" : null,
    "verdict" : "NONE",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "teststeps" : [ {
      "name" : "m",
      "description" : null,
      "verdict" : "NONE",
      "expectedResult" : "xyz",
      "dType" : "TestStep"
    }, {
      "name" : "n",
      "description" : null,
      "verdict" : "PASSED",
      "expectedResult" : "xyz",
      "dType" : "TestStep"
    }, {
      "teststeps" : [ {
        "name" : "p",
        "description" : null,
        "verdict" : "PASSED",
        "expectedResult" : "xyz",
        "dType" : "TestStep"
      } ],
      "name" : "o",
      "description" : null,
      "verdict" : "PASSED",
      "expectedResult" : "xyz",
      "dType" : "TestStepFolder"
    } ],
    "name" : "l",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStepFolder"
  }, {
    "teststeps" : [ {
      "name" : "r",
      "description" : null,
      "verdict" : "PASSED",
      "expectedResult" : "xyz",
      "dType" : "TestStep"
    } ],
    "name" : "q",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStepFolder"
  }, {
    "teststeps" : [ {
      "teststeps" : [ {
        "name" : "u",
        "description" : null,
        "verdict" : "NONE",
        "expectedResult" : "xyz",
        "dType" : "TestStep"
      } ],
      "name" : "t",
      "description" : "null",
      "verdict" : "NONE",
      "expectedResult" : "xyz",
      "dType" : "TestStepFolder"
    } ],
    "name" : "s",
    "description" : null,
    "verdict" : "NONE",
    "expectedResult" : "xyz",
    "dType" : "TestStepFolder"
  } ],
  "name" : "h",
  "description" : null,
  "verdict" : "PASSED",
  "expectedResult" : "xyz",
  "dType" : "TestStepFolder"
}, {
  "teststeps" : [ {
    "teststeps" : [ {
      "name" : "x",
      "description" : null,
      "verdict" : "NONE",
      "expectedResult" : "xyz",
      "dType" : "TestStep"
    } ],
    "name" : "w",
    "description" : null,
    "verdict" : "NONE",
    "expectedResult" : "xyz",
    "dType" : "TestStepFolder"
  }, {
    "name" : "z",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "name" : "z",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "name" : "aa",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "name" : "ab",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  } ],
  "name" : "v",
  "description" : null,
  "verdict" : "PASSED",
  "expectedResult" : "xyz",
  "dType" : "TestStepFolder"
}, {
  "teststeps" : [ {
    "name" : "ad",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "name" : "ae",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  }, {
    "name" : "af",
    "description" : null,
    "verdict" : "PASSED",
    "expectedResult" : "xyz",
    "dType" : "TestStep"
  } ],
  "name" : "ac",
  "description" : null,
  "verdict" : "PASSED",
  "expectedResult" : "xyz",
  "dType" : "TestStepFolder"
}, {
  "name" : "ag",
  "description" : null,
  "verdict" : "PASSED",
  "expectedResult" : "xyz",
  "dType" : "TestStep"
}, {
  "name" : "ah",
  "description" : null,
  "verdict" : "NONE",
  "expectedResult" : "xyz",
  "dType" : "TestStep"
}]

[JPollet]

You could try this workflow : actually I don’t understand the data but the structure of the json can be parse (with name instead of expected result).
json.knwf (31,7 Ko)

Best,

[simonmng]

yes this is a mistake from my side, i want to parse name and verdict. I work with knime 5.2.5 when i try to use your workflow it doesnt find this node

[JPollet]

Indeed I use the new Row Filter with Knime 5.3 : insert the old node Row filter and filter on row with json not missing. These rows are to be passed on the recursion port the extract data recursively.
Joel

[JPollet]

Like this :

[gonhaddock]

Hi @simonmng
You can use the workflow from the previous topic:

Extract number sequence

Hello @simonmng It seems to me that the structure of a JSON file is not standard, the square brackets correlation is missed; besides that, there’s a comma missed in the group separation… the structure should be like this (being aware of quotation type): [{"rel": "self", "href": "https://.net/api/report/eExecution/12345678"}, { "rel": "self", "href": "https://oup.net/api/ret/cution/12345677"}] Take a look in this post: Then a standard JSON extract workflow can be applied. You should check y…

You would have just to call single queries for the desired fields, in the ‘JSON Path’ node.

BR

[simonmng]

your workflow works with the examples, but i have so many cells with JSONs in this type like in the examples I get that many results and the order doesnt works

at the end i want that there is one column where the result of the parsing of one JSON stands like from the example in the beginning in one cell like this:

2.1 a-NONE, 2.1.1 b-NONE, 2.2 c-PASSED, 2.2.1 d-NONE, 2.2.2 e-NONE, 2.2.3 f-PASSED, …

[JPollet]

Hi @simonmng,
Indeed the workflow just map Row0 to 2 : it’ arbitrary and static to fit your example. Something can be done more dynamically in the string manipulation after adding in the beginning a counter properly define.
The workflow here seems to work with two json without anymore modification. But the two json have a same structure. Perhaps it is more secure to make a loop (see the second proposition in the same .knwf).
You can modify the json identifier as you want of course.
json_v1.knwf (79,8 Ko)

Best,
Joel

[simonmng]

thank you very much for your help, very appreciate it

Do you know where this error comes from when I start your workflow?

[JPollet]

Perhaps because I use here too the new RowId node : see the configuration panel for just create a new column with rowid and name Number.

I just tested with two different json : chunk loop seems to be unnecessary.
Joel

[JPollet]

By the way, I don’t understand why the error is about flow variable : there is no need of this and no modification of this type.
Joel

[simonmng]

Thank you very much, you helped me a lot!
Appreciate it!!!

[gonhaddock]

Hi @simonmng
Just for the records as the topic has been already solved:

The only problem I see in the JSON; it’s that the data items are allocated at two hierarchy levels.

20240910_parsing_complicate_JSON_v00.knwf (27.0 KB)

BR

[JPollet]

Hi @gonhaddock,
Indeed but it seems that the hierarchy is sufficiant for the purpose and reflect in the structure of the number. The dType seems to be unnecessary in this case.
Joel

[simonmng]

Hello Joel, when I use your workflow for my data, I dont get to the correct sequence. I want everything to be in 1,2,3,4,5 steps and the testfolders always get the first numbers. So its like 1 Testfolder, 2 Testfolder, 3 Testfolder…, 8 Teststep, 9 Teststep,…
In which point is this assign of numbers done?

Greetings Simon

[JPollet]

Hello @simonmng,
See the Ungroup nodes (during iteration of the loop) : they modify the RowId accordingly with the structure. The fact is that, at the end of the file you provide, there is no dType Testfolder for name ag and ah.
But perhaps I don’t exactly understand what is expected !
Joel