Question

How can I create a nested JSON from an Excel- or CSV-file?

  • 24 March 2020
  • 5 replies
  • 238 views
S
Badge +2

I have a Excel-file with the following content: 

group1group2group3nameinfoGeneralNation

 

Philinfo1GeneralNation

 

Kareninfo2GeneralMunicipality

 

Billinfo3GeneralMunicipality

 

Paulinfo4SpecificProvince

 

Patrickinfo5SpecificProvince

 

Maikelinfo6SpecificProvinceGovernanceMikeinfo7SpecificProvinceGovernanceLukeinfo8SpecificDistrict

 

Mariainfo9SpecificDistrict

 

Davidinfo10

 

The attributes can be adjusted it is a Excel-file; I create the file myself.

I would like to automatically create a nested JSON file. 

This should look like this:

  "name": "Overview",
    "children": [{
        "name": "General", 
            "children": [
                { "name": "Nation",
                    "children": [
                        {"name": "Phil", "info": "info1"},
                        {"name": "Karen", "info": "info2"} 
                    ]
                },
                { "name": "Municipality",
                    "children": [
                        {"name": "Bill", "info": "info3"},
                        {"name": "Paul", "info": "info4"} 
                    ]        
                }
            ]
                
                        
  }, 
  {
        "name": "Specific", 
            "children": [
                { "name": "Province",
                    "children": [
                        {"name": "Patrick", "info": "info5"},
                        {"name": "Maikel", "info": "info6"},
                        {"name": "Governance",
                            "children": [
                                {"name": "Mike", "info": "info7"},
                                {"name": "Luke", "info": "info8"}
                            ]
                        }
                    ]
                },
                { "name": "District",
                    "children": [
                        {"name": "Maria", "info": "info9"},
                        {"name": "David", "info": "info10"} 
                    ]        
                }
            ]
  }
  ]

I tried the JSONTemplater transformer, but I can't figure out how this works. 

I am not sure where to put the template, either in the root or in the sub.

 

Hope someone can help me with this!

 

Kind regards,

Stefan

5 replies

deanatsafe
Rank
Badge +6

The following JSON Tutorial has a JSONTemplater example very similar to the one you are describing here. If you are new to JSON writing, I would strongly recommend working through this example to get a better feel for how the templating process works for JSON generation:

https://knowledge.safe.com/articles/44294/json-writing-with-jsontemplater.html

To start with, your root template will look very similar. The root template is the part that doesnt repeat. It has only one input feature, often from a Creator set to generate a single feature. For JSON writing, often the root template is very simple and is just there to wrap the whole document and call the subtemplates. In your case it might look something like:

{|

fme:process-features("Children")

|}

Assuming 'Children' is the name of your top level subtemplate. Since everything else repeats, the rest of your JSON structure will go into a series of sub-templates. Note that JSON is often more deeply nested than relational tables which are limited to one level of nesting: tables composed of fields, so this is why sometimes even sub templates have sub templates. So you don't always have a 1 to 1 correspondence between input feature types and subtemplates.

The tutorial example has 2 subtemplates which are essentially children and grandchildren to the parent root template. As you can see from the tutorial, each level of nesting uses a copy of the data stream with an appropriate group-by. In your case you would likely want to have separate Samplers with the group-by set to group1, group2, and group3 respectively.

Give this a try, first with the tutorial and then with your data, and if you get stuck let us know and we can help you further.

S
Badge +2

The following JSON Tutorial has a JSONTemplater example very similar to the one you are describing here. If you are new to JSON writing, I would strongly recommend working through this example to get a better feel for how the templating process works for JSON generation:

https://knowledge.safe.com/articles/44294/json-writing-with-jsontemplater.html

To start with, your root template will look very similar. The root template is the part that doesnt repeat. It has only one input feature, often from a Creator set to generate a single feature. For JSON writing, often the root template is very simple and is just there to wrap the whole document and call the subtemplates. In your case it might look something like:

{|

fme:process-features("Children")

|}

Assuming 'Children' is the name of your top level subtemplate. Since everything else repeats, the rest of your JSON structure will go into a series of sub-templates. Note that JSON is often more deeply nested than relational tables which are limited to one level of nesting: tables composed of fields, so this is why sometimes even sub templates have sub templates. So you don't always have a 1 to 1 correspondence between input feature types and subtemplates.

The tutorial example has 2 subtemplates which are essentially children and grandchildren to the parent root template. As you can see from the tutorial, each level of nesting uses a copy of the data stream with an appropriate group-by. In your case you would likely want to have separate Samplers with the group-by set to group1, group2, and group3 respectively.

Give this a try, first with the tutorial and then with your data, and if you get stuck let us know and we can help you further.

Thanks for your elaborated answer. This is very useful. It does work to some extend. The problem I face now, however is that there are records which do not have a value in attribute group3. If I add this group in a subtemplate, I get an error.

I have added the workspace.

nestedJSON example.fmwt

 

 

 

ebygomm
Rank
Userlevel 1
Badge +21

Thanks for your elaborated answer. This is very useful. It does work to some extend. The problem I face now, however is that there are records which do not have a value in attribute group3. If I add this group in a subtemplate, I get an error.

I have added the workspace.

nestedJSON example.fmwt

 

 

 

You can write if then else statements

There's an example here

https://knowledge.safe.com/questions/91602/json-templater-how-to-export-attributes-with-value.html

S
Badge +2

This very useful! Thanks. I did make it work with my example. I used the following code in the SUB3 expression. 

{
    if (fme:has-attribute("group3"))
    then 
        {
            fme:get-attribute("group3") : [
            fme:process-features("SUB4", "group3", fme:get-attribute("group3"))
            ]
        }
    else 
        {
    "Name" : fme:get-attribute("name"),
    "Info" : fme:get-attribute("info")
        }
}

 

There is one more thing I can't figure out. The result I have so far is ( a part of it)

{
   "General" : [
      {
         "Nation" : [
            {
               "Name" : "Phil", "Info" : "info1"
            },
            {
               "Name" : "Karen", "Info" : "info2"
            }
         ]
      },
      {
         "Municipality" : [
            {
               "Name" : "Bill", "Info" : "info3"
            },
            {
               "Name" : "Paul", "Info" : "info4"
            }
         ]
      }
   ],

 

However it should include the text "children", but I don't know where to put this text in the expression. I have tried multiple things. 

 

    "children": [{
        "name": "General", 
            "children": [
                { "name": "Nation",
                    "children": [
                        {"name": "Phil", "info": "info1"},
                        {"name": "Karen", "info": "info2"} 
                    ]
                },
                { "name": "Municipality",
                    "children": [
                        {"name": "Bill", "info": "info3"},
                        {"name": "Paul", "info": "info4"} 
                    ]        
                }
            ]    

nestedJSON example 2.fmwt

S
Badge +2

This very useful! Thanks. I did make it work with my example. I used the following code in the SUB3 expression. 

{
    if (fme:has-attribute("group3"))
    then 
        {
            fme:get-attribute("group3") : [
            fme:process-features("SUB4", "group3", fme:get-attribute("group3"))
            ]
        }
    else 
        {
    "Name" : fme:get-attribute("name"),
    "Info" : fme:get-attribute("info")
        }
}

 

There is one more thing I can't figure out. The result I have so far is ( a part of it)

{
   "General" : [
      {
         "Nation" : [
            {
               "Name" : "Phil", "Info" : "info1"
            },
            {
               "Name" : "Karen", "Info" : "info2"
            }
         ]
      },
      {
         "Municipality" : [
            {
               "Name" : "Bill", "Info" : "info3"
            },
            {
               "Name" : "Paul", "Info" : "info4"
            }
         ]
      }
   ],

 

However it should include the text "children", but I don't know where to put this text in the expression. I have tried multiple things. 

 

    "children": [{
        "name": "General", 
            "children": [
                { "name": "Nation",
                    "children": [
                        {"name": "Phil", "info": "info1"},
                        {"name": "Karen", "info": "info2"} 
                    ]
                },
                { "name": "Municipality",
                    "children": [
                        {"name": "Bill", "info": "info3"},
                        {"name": "Paul", "info": "info4"} 
                    ]        
                }
            ]    

nestedJSON example 2.fmwt

@deanatsafe

Hi Dean, I still haven't figured out how I could add an attribute name (not sure how to call it) to the nested JSON. So it includes the texts "name" and "children" before the actual text from the csv is added. 

Hope you can help. 

Thanks Stefan

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
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.