Okay, this question might be a bit difficult. I am NOT a veteran JSON user, but here we go anyways..

I have a HUGE file of items from a videogame. The problem is, the JSON is formatted in such a way, that data is pretty hard to call.

Example:

        "@uniquename": "T3_MEAL_SOUP",
        "@weight": "3.15",
        "@nutrition": "252",
        "craftingrequirements": {
            "@craftingfocus": "168",
            "craftresource": {
                "@uniquename": "T3_WHEAT",
                "@count": "48"
            }
        },
        "enchantments": {
            "enchantment": [
                {
                    "@enchantmentlevel": "1",
                    "craftingrequirements": {
                        "@craftingfocus": "235",
                        "craftresource": [
                            {
                                "@uniquename": "T3_WHEAT",
                                "@count": "48"
                            },
                            {
                                "@uniquename": "T1_FISHSAUCE_LEVEL1",
                                "@count": "30"
                            }
                        ]
                    }
                },
                {
                    "@enchantmentlevel": "2",
                    "craftingrequirements": {
                        "@craftingfocus": "368",
                        "craftresource": [
                            {
                                "@uniquename": "T3_WHEAT",
                                "@count": "48"
                            },
                            {
                                "@uniquename": "T1_FISHSAUCE_LEVEL2",
                                "@count": "30"
                            }
                        ]
                    }
                },
                {
                    "@enchantmentlevel": "3",
                    "craftingrequirements": {
                        "@craftingfocus": "769",
                        "craftresource": [
                            {
                                "@uniquename": "T3_WHEAT",
                                "@count": "48"
                            },
                            {
                                "@uniquename": "T1_FISHSAUCE_LEVEL3",
                                "@count": "30"
                            }
                        ]
                    }
                }
            ]
        }
    },

I am trying to use either Power Query in Excel or “ImportJSON” in Google sheets to get data from this JSON.

I need these “recipes” to be unique to the point where the final version of the file should look like this insted.

       {
       "@uniquename": "T3_MEAL_SOUP",
       "@weight": "3.15",
       "@nutrition": "252",
       "craftingrequirements": {
           "@craftingfocus": "168",
           "craftresource": {
               "@uniquename": "T3_WHEAT",
               "@count": "48"
           }
       },
       {
       "@uniquename": "T3_MEAL_SOUP@1",
       "@weight": "3.15",
       "@nutrition": "252",
       "craftingrequirements": {
           "@craftingfocus": "235",
           "craftresource": {
               "@uniquename": "T3_WHEAT",
               "@count": "48",
               "@uniquename1": "T1_FISHSAUCE_LEVEL1",
               "@count1": "30"
           },
           {
               "@uniquename": "T3_MEAL_SOUP@2",
               "@weight": "3.15",
               "@nutrition": "252",
               "craftingrequirements": {
                   "@craftingfocus": "368",
                   "craftresource": {
                       "@uniquename": "T3_WHEAT",
                       "@count": "48",
                       "@uniquename1": "T1_FISHSAUCE_LEVEL2",
                       "@count1": "30"
                   }
       }}}, 

Basically it takes the “Enchantments” object and turns that into a new object completely, so it’s not nested in the first object. Gives it a new name “T3_MEAL_SOUP@1” and adds the recipe underneath.

The problems I am having with the import to Excel and Google Sheets, is that because some of the items have only 1 “craftresource” while others have up till 4 items in an array, the data is really hard to get consitently.

I hope someone can help me with my issue.

I have uploaded the full raw file here, so it might be easier to understand what I am trying to do..

https://raw.githubusercontent.com/rasmuslhornung/AlbionData/main/Food_Formatted.json