Skip to main content
Solved

how can I flatten json objects when they have different names?

  • March 13, 2020
  • 1 reply
  • 16 views
R
Forum|alt.badge.img
"2046": {
        "ID": "020935",
        "Address": "Bagg Close 9, 29643 Churchtown",
        "Provider": [
            "D2 - GSM09",
            "D2 - LTE08"
        ],
        "Locationdocuments": {
            "stob": {
                "date": "/Date(1412546400000)/",
                "Applicant": "Vodafone",
                "STOB": "c8859eec-63b2-4a75-a2c2-00a586110005",
                "TD": "74c192b2-0067-40fc-a41d-f5b5f73afbf1",
                "BZ": "0eb422f8-0561-4f4f-8324-d7110ff56ff0",
                "LP": "0b37ed04-4d01-4f07-b67f-d8bd2590a66c",
                "AD": "c019cc03-c812-4816-8024-407cedeb461c",
                "IBA": "9684e283-10e6-4e95-8931-3906fd3a62cb"
            },
            "12.09.2014": {
                "date": "/Date(1412546400000)/",
                "Applicant": "Vodafone",
                "STOB": "c8859eec-63b2-4a75-a2c2-00a586110005",
                "TD": "74c192b2-0067-40fc-a41d-f5b5f73afbf1",
                "BZ": "0eb422f8-0561-4f4f-8324-d7110ff56ff0",
                "LP": "0b37ed04-4d01-4f07-b67f-d8bd2590a66c",
                "AD": "c019cc03-c812-4816-8024-407cedeb461c",
                "IBA": "9684e283-10e6-4e95-8931-3906fd3a62cb"
            }
        },
        "coord_north": 53.024721,
        "coord_east": 9.758055
    },

"548200": {.,..etc etc

 

 

Here is my json created by the jsonfragmenter. I need to flatten the json so that I have a feature for each dataset ( in this case "020935", and ID,Address,Provider as attribute fields. The Attribute name I need to expose ("2046") changes each time in the given structure so that I can't define it in the transformer.

Also difficult is how to pivot the Provider array into attribute values.

My result should look something like this:

 

2046IDAddressProviderstobTDBZLPAD

 

 

Best answer by hollyatsafe

Hi @robertdbuckley,

Given the JSON provided I think you will have to flatten this using a second JSONFragmenter, this way each feature parsed out will be the JSON from each different object.

 

 

You can leave the JSON Query set to json[*] where the * will represent the JSON objects of different names. Then in the same transformer can be set up to also flatten the resulting JSON from each object to get at the individual attributes you'd like to expose.

The Provider is a List, so to expose this you will have to use the {} and then you may want to perform some concatenation to be able to have this as an attribute.

Then for the stob, TD etc are nested in further JSON, so to expose the as attributes you will have to include the Locationdocuments.stob. prefix to get to this layer. Therefore after you have flattened these you may want to perform a bulk rename of the attributes to remove this prefix.

I have attached an example workspace to demonstrate this. jsonfragmenter.fmw

View original
Did this help you find an answer to your question?

1 reply

H
Forum|alt.badge.img+2
  • hollyatsafe
  • Best Answer
  • March 13, 2020

Hi @robertdbuckley,

Given the JSON provided I think you will have to flatten this using a second JSONFragmenter, this way each feature parsed out will be the JSON from each different object.

 

 

You can leave the JSON Query set to json[*] where the * will represent the JSON objects of different names. Then in the same transformer can be set up to also flatten the resulting JSON from each object to get at the individual attributes you'd like to expose.

The Provider is a List, so to expose this you will have to use the {} and then you may want to perform some concatenation to be able to have this as an attribute.

Then for the stob, TD etc are nested in further JSON, so to expose the as attributes you will have to include the Locationdocuments.stob. prefix to get to this layer. Therefore after you have flattened these you may want to perform a bulk rename of the attributes to remove this prefix.

I have attached an example workspace to demonstrate this. jsonfragmenter.fmw

Reply


Helpful Members This Week

Community Stats

31,191
Posts
118,244
Replies
38,801
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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