Skip to main content
  • EN

I have this json file (see the attached txt file) that I want to load into a table (shown as in the attached excel file). I have done many json conversions over the years, but this one puzzled me quite a bit and could not figure out a good way to do it. The difficult part is that for each asset Id, there could be a few years of emissions. Each emission has two emission type, n2o and co2.  How can I capture each year and its emissions into rows based on the emission type to be pivoted? 

{
  "assets": "
    {
      "Id": 25494428,
       "Emissions":
        {
          "2021": b
            {
              "EmissionsFactor": 1,
              "EmissionsFactorUnits": "U1",
              "n2o": 10
            },
            {
              "EmissionsFactor": 2,
              "EmissionsFactorUnits": "U2",
              "co2": 100
            }
          ]
        },
        {
          "2022":
            {
              "EmissionsFactor": 3,
              "EmissionsFactorUnits": "U1",
              "n2o": 11
            },
            {
              "EmissionsFactor": 4,
              "EmissionsFactorUnits": "U2",
              "co2": 110
            }
          ]
         }
        ]
    },
    {
      "Id": 25494602,
       "Emissions":
        {
          "2020":
            {
              "EmissionsFactor": 2,
              "EmissionsFactorUnits": "U3",
              "n2o": 12
            },
            {

              "EmissionsFactor": 4,
              "EmissionsFactorUnits": "U4",
              "co2": 120
            }
          ]
        },
        {
          "2019":
            {
              "EmissionsFactor": 3,
              "EmissionsFactorUnits": "U3",
              "n2o": 13
            },
            {
              "EmissionsFactor": 5,
              "EmissionsFactorUnits": "U4",
              "co2": 130
            }
          ]
        },
         {
          "2017":
            {
              "EmissionsFactor": 4,
              "EmissionsFactorUnits": "U1",
              "n2o": 14
            },
            {
              "EmissionsFactor": 5,
              "EmissionsFactorUnits": "U2",
              "co2": 130
            }
          ]
        }
      ]
    }
  ]
}

I am not the best with JSON, so I am thinking there is probably a better way, but here is what I came up with for now:

I started by just removing the first two lines and the last line. The “assets” parts, that way each Id would be its own object inside the text file and not inside an array. I just used notepad for this because I had the file open to review it after downloading.
For the JSON reader, I set “Flatten Nested JSON Values into Attributes” to No. With the example txt file you provided, this read in two features. I then used two JSONFragmenters to fragment the first two levels, an AttributeManager to save out the index as the Year, and then another JSONFragmenter set to flatten and expose the 4 attributes. Then I used an AttributeManager to create the EmissionType and EmissionValue attributes with conditional values.


I did this in 2021 version, workspace attached in zip folder

@liamfez , fantastic, thanks a lot for the great help. You showed me how to use json_index to capture the key and attached it to an new attribute. 

Reply


Community Stats

30,147
Posts
114,428
Replies
37,789
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.