JSON parse JSON with changing Key and changing type of value (list or string)

36 views Asked by At

I have JSON Rows which can change key and type of values

changing keys in examples are

015913d2e43d41ef98e6e5c8dc90cd09_2_1 and e8c93befe4a34bcabbf604e352a41a2d_2_1

changing types of values are

list (Row 1):

"answer": [
          "<i>GET</i>\n",
          "<i>PUT</i>\n",
          "<i>POST</i>\n",
          "<i>TRACE</i>\n",
          "<i>HEAD</i>\n",
          "<i>DELETE</i>\n"
        ],

text (Row 2):

"answer": "Может быть во всех",

Examples of rows:

row 1

{
  "event": {
    "submission": {
      "015913d2e43d41ef98e6e5c8dc90cd09_2_1": {
        "question": "Какие виды <i>HTTP</i> запросов могут внести изменения на сервере (в общем случае)?",
        "answer": [
          "<i>GET</i>\n",
          "<i>PUT</i>\n",
          "<i>POST</i>\n",
          "<i>TRACE</i>\n",
          "<i>HEAD</i>\n",
          "<i>DELETE</i>\n"
        ],
        "response_type": "choiceresponse",
        "input_type": "checkboxgroup",
        "correct": false,
        "variant": "",
        "group_label": ""
      }
    }
  }
}

row 2

{
  "event": {
    "submission": {
      "e8c93befe4a34bcabbf604e352a41a2d_2_1": {
        "question": "В запросах какого типа может быть использована <i>RCE</i>?",
        "answer": "Может быть во всех",
        "response_type": "multiplechoiceresponse",
        "input_type": "choicegroup",
        "correct": true,
        "variant": "",
        "group_label": ""
      }
    }
  }
}

Here is the Image in Power Query

How can extract data from the List in Direct Power Query?

If I write this

= Table.AddColumn(#"Duplicated Column", "answer_s", each Record.Field([raw_event][event][submission],[question_id])[answer]{0})

I get error with text types answers image, error with text types

If i write this: = Table.AddColumn(#"Duplicated Column", "answer_s", each Record.Field([raw_event][event][submission],[question_id])[answer])

I get Lists

I have already asked the question with parsing JSON JSON parsing with changing keys

the result I would like to receive should be looks like this thanks!

0

There are 0 answers