Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Data Cleaning Badly Designed JSON Data — Need advice on best strategy

OnlyDean
March 13, 2023
276 views
0 votes
2 Answers

I have over 1,000 JSON files representing game elements. This is a typical example:

{
    "name": "Reeve Hunter",
    "edition": 3,
    "revision": "2021 v1",
    "keywords": [
        "Circle",
        "Wolf Sworn",
        "Reeve of Orboros",
        "Solo"
    ],
    "point cost": 4,
    "field allowance": 2,
    "models": [
        {
            "Hunter": {
                "spd": 6,
                "str": 6,
                "mat": 6,
                "rat": 7,
                "def": 13,
                "arm": 13,
                "cmd": 7,
                "boxes": 5,
                "advantages": [
                    "Advance Deploy",
                    "Assault",
                    "Pathfinder"
                ],
                "base size": 30,
                "attacks": [
                    {
                        "Double Crossbow": {
                            "type": "ranged",
                            "rng": 12,
                            "rof": 2,
                            "pow": 10
                        }
                    },
                    {
                        "Battle Blade": {
                            "type": "melee",
                            "rng": 0.5,
                            "pow": 3,
                            "p+s": 9
                        }
                    },
                    {
                        "Cleft Sword": {
                            "type": "melee",
                            "rng": 1,
                            "pow": 5,
                            "p+s": 11,
                            "weapon qualities": ["Weapon Master"],
                            "abilities": ["Powerful Charge"]
                        }
                    }
                ],
                "abilities": [
                    "Hunter",
                    "Leadership [Reeves of Orboros]",
                    "Quickwork",
                    "Sprint"
                ]
            }
        }
    ]
}

While the above is valid JSON, once I got all the JSON files into a MongoDB database I realized I couldn’t analyze my data properly because every sub-document in the models and attacks arrays had a unique name as the key.

I want to transform each JSON document like this:

{
  "name": "Reeve Hunter",
  "edition": 3,
  "revision": "2021 v1",
  "keywords": [
    "Circle",
    "Wolf Sworn",
    "Reeve of Orboros",
    "Solo"
  ],
  "point cost": 4,
  "field allowance": 2,
  "models": [
    {
      "model name": "Hunter",
      "spd": 6,
      "str": 6,
      "mat": 6,
      "rat": 7,
      "def": 13,
      "arm": 13,
      "cmd": 7,
      "boxes": 5,
      "advantages": [
        "Advance Deploy",
        "Assault",
        "Pathfinder"
      ],
      "base size": 30,
      "attacks": [
        {
          "attack name": "Double Crossbow",
          "type": "ranged",
          "rng": 12,
          "rof": 2,
          "pow": 10
        },
        {
          "attack name": "Battle Blade",
          "type": "melee",
          "rng": 0.5,
          "pow": 3,
          "p+s": 9
        },
        {
          "attack name": "Cleft Sword",
          "type": "melee",
          "rng": 1,
          "pow": 5,
          "p+s": 11,
          "weapon qualities": [
            "Weapon Master"
          ],
          "abilities": [
            "Powerful Charge"
          ]
        }
      ],
      "abilities": [
        "Hunter",
        "Leadership [Reeves of Orboros]",
        "Quickwork",
        "Sprint"
      ]
    }
  ]
}

I’m an old hat at using awk and sed for cleaning up text, but this transformation is complex and features enough "gotchas" that I feel like I need a different strategy.

I’ve been using the wonderful tool jq to manipulate and query JSON files, and I feel like there has to be a way to leverage its power to do what I need.

Does anyone have any advice on the best strategy to write a script that can transform all of my data into the cleaner format I require?

Tags: data-cleaning jq json mongodb text

Answers

You could use to_entries to decompose the nested objects into .key and .value, then rearrange and update using |=:

<file.json jq '.models |= map(to_entries[] | {"model name": .key} + .value)
  | .models[].attacks |= map(to_entries[] | {"attack name": .key} + .value)'

{
  "name": "Reeve Hunter",
  "edition": 3,
  "revision": "2021 v1",
  "keywords": [
    "Circle",
    "Wolf Sworn",
    "Reeve of Orboros",
    "Solo"
  ],
  "point cost": 4,
  "field allowance": 2,
  "models": [
    {
      "model name": "Hunter",
      "spd": 6,
      "str": 6,
      "mat": 6,
      "rat": 7,
      "def": 13,
      "arm": 13,
      "cmd": 7,
      "boxes": 5,
      "advantages": [
        "Advance Deploy",
        "Assault",
        "Pathfinder"
      ],
      "base size": 30,
      "attacks": [
        {
          "attack name": "Double Crossbow",
          "type": "ranged",
          "rng": 12,
          "rof": 2,
          "pow": 10
        },
        {
          "attack name": "Battle Blade",
          "type": "melee",
          "rng": 0.5,
          "pow": 3,
          "p+s": 9
        },
        {
          "attack name": "Cleft Sword",
          "type": "melee",
          "rng": 1,
          "pow": 5,
          "p+s": 11,
          "weapon qualities": [
            "Weapon Master"
          ],
          "abilities": [
            "Powerful Charge"
          ]
        }
      ],
      "abilities": [
        "Hunter",
        "Leadership [Reeves of Orboros]",
        "Quickwork",
        "Sprint"
      ]
    }
  ]
}

Demo

If you have these JSON as files, then jq would be one of the best tools, I assume. However, if you have stored them in a MongoDB, then I would propose this update:

db.collection.updateMany(
   {},
   [
      {
         $set: {
            models: {
               $map: {
                  input: "$models",
                  in: {
                     $let: {
                        vars: { model: { $objectToArray: "$$this" } },
                        in: {
                           $mergeObjects: [
                              { "model name": { $first: "$$model.k" } },
                              { $first: "$$model.v" }
                           ]
                        }
                     }
                  }
               }
            }
         }
      },
      {
         $set: {
            models: {
               $map: {
                  input: "$models",
                  as: "model",
                  in: {
                     $mergeObjects: [
                        "$$model",
                        {
                           attacks: {
                              $map: {
                                 input: "$$model.attacks",
                                 in: {
                                    $let: {
                                       vars: { attack: { $objectToArray: "$$this" } },
                                       in: {
                                          $mergeObjects: [
                                             { "attack name": { $first: "$$attack.k" } },
                                             { $first: "$$attack.v" }
                                          ]
                                       }
                                    }
                                 }
                              }
                           }
                        }
                     ]
                  }
               }
            }
         }
      }
   ]
)

Please signup or login to give your own answer.

Click here to cancel reply.