Solved

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

  • 13 March 2020
  • 1 reply
  • 4 views
R
Badge 
"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

 

 

icon

Best answer by hollyatsafe 13 March 2020, 19:29

View original

1 reply

H
Badge +2

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


Community Stats

28,700
Posts
109,456
Replies
36,549
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.