Skip to main content
Solved

How to transform this json file to a table?

  • October 11, 2024
  • 2 replies
  • 117 views
aguan
Supporter
Forum|alt.badge.img+11

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": [
            {
              "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
            }
          ]
        }
      ]
    }
  ]
}

Best answer by liamfez

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

2 Attachments
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

2 replies

liamfez
Influencer
Forum|alt.badge.img+44
  • liamfezInfluencer
  • Influencer
  • Best Answer
  • October 11, 2024

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

1 Attachments
Rotate monitor to the right to improve translation speed using gravity
danilo_fme
aguan
aguan
Supporter
Forum|alt.badge.img+11
  • aguanSupporter
  • Author
  • Supporter
  • October 12, 2024

@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. 

danilo_fme
liamfez

Community Stats

32,154
Posts
122,043
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.