JSON parsing with changing keys

Question

when I try to parse answers in JSON, I can not receive the data I need as the key (Row 1 - **ecf2f5d0c94747a595765cca40443262_2_1**, Row 2 - **ecf2f5d0c94747a595765cca40443262_2_123**) is always changing.

Examples:

JSON Row 1

{
  "event": {
    "problem_id": "block-v1:SkillFactory+ADMIN-FREE+2021+type@problem+block@ecf2f5d0c94747a595765cca40443262",
    "answers": {
      "**ecf2f5d0c94747a595765cca40443262_2_1**": "34"
    },
    "submission": {
      "ecf2f5d0c94747a595765cca40443262_2_1": {
        "question": "",
        "answer": "34",
        "response_type": "numericalresponse",
        "input_type": "formulaequationinput",
        "correct": false,
        "variant": "",
        "group_label": ""
      }
    }
  }
}

JSON Row 2

{
  "event": {
    "problem_id": "block-v1:SkillFactory+ADMIN-FREE+2021+type@problem+block@ecf2f5d0c94747a595765cca40443262",
    "answers": {
      "**ecf2f5d0c94747a595765cca40443262_2_123**": "3433"
    },
    "submission": {
      "ecf2f5d0c94747a595765cca40443262_2_123": {
        "question": "",
        "answer": "3433",
        "response_type": "numericalresponse",
        "input_type": "formulaequationinput",
        "correct": false,
        "variant": "",
        "group_label": ""
      }
    }
  }
}

the ideal solution should be different columns.

I found this solution but it did not help.

horseyride · Accepted Answer · 2024-03-22 11:50:51Z

1

Try

let Source = Json.Document(File.Contents("C:\Temp\a.json")),
ListToTable = Table.FromRecords(Source),
#"Added Custom1" = Table.AddColumn(ListToTable, "Custom.1", each Record.FieldNames([event][answers]){0}),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each Record.Field([event][answers],[Custom.1]))
in  #"Added Custom"

which gets you

[ { "event": { "problem_id": "block-v1:SkillFactory+ADMIN-FREE+2021+type@problem+block@ecf2f5d0c94747a595765cca40443262", "answers": { "ecf2f5d0c94747a595765cca40443262_2_1": "34" }, "submission": { "ecf2f5d0c94747a595765cca40443262_2_1": { "question": "", "answer": "34", "response_type": "numericalresponse", "input_type": "formulaequationinput", "correct": false, "variant": "", "group_label": "" } } } }, { "event": { "problem_id": "block-v1:SkillFactory+ADMIN-FREE+2021+type@problem+block@ecf2f5d0c94747a595765cca40443262", "answers": { "ecf2f5d0c94747a595765cca40443262_2_123": "3433" }, "submission": { "ecf2f5d0c94747a595765cca40443262_2_123": { "question": "", "answer": "3433", "response_type": "numericalresponse", "input_type": "formulaequationinput", "correct": false, "variant": "", "group_label": "" } } } } ]

answered Mar 22, 2024 at 11:50

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

horseyride Over a year ago

then please toggle the checkmark next to the arrows to mark issue as solved

Collectives™ on Stack Overflow

JSON parsing with changing keys

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related