Skip to main content
Solved

Extract nested data from JSON to attribute table

  • June 28, 2024
  • 1 reply
  • 94 views

bubblebeb
Contributor
Forum|alt.badge.img+6

Hi. I have the JSON data below and I have managed to expose attributes from this using the JSONFragmenter but am stuck with trying the extract the thirdparty.names from this record. 

As there are multiple names in this nested array I need to extract them either as a comma separated attribute. i.e. Leicestershire County Council, Mr J Tomms. Or as a list that I can work from. 

I cant seem to expose this attribute due to it’s nesting. 

Any help with the correct transformer and configuration would be great. 

Currently using FME Form 2023.2.2 but can update to 2024 if it is easier. 

 

         {
            "_index" : "xxx",
            "_type" : "_doc",
            "_id" : "667951b042fcaa46307d1935",
            "_score" : null,
            "_source" : {
               "coi_num" : 68902,
               "engagement" : {
                  "mail" : "Bob@home.com",
                  "objectguid" : "8dc75819103e4e408684a75f87fd320b",
                  "dn" : "CN=Bob Smith,OU=Rural,OU=Worcester,OU=FGUsers,DC=fg,DC=local",
                  "_id" : "56bc84793b76c8680600008a",
                  "cn" : "Bob Smith",
                  "samaccountname" : "BSmith"
               },
               "recurring" : {
                  "period" : null
               },
               "center" : {
                  "lng" : -1.058,
                  "lat" : 52.621
               },
               "show" : "2024-06-24T11:00:00.889Z",
               "check" : {
                  "types" : true
               },
               "type" : "new",
               "thirdparty" : [
                  {
                     "address" : "Farms & Estates, County Hall, Glenfield, Leicester, LE3 8RE ",
                     "related" : null,
                     "name" : "Leicestershire County Council ",
                     "type" : "Landlord / Tenant"
                  },
                  {
                     "address" : "James Farm, Kilby Road, Leicestershire",
                     "related" : null,
                     "name" : "Mr J Tomms ",
                     "type" : "Landlord / Tenant"
                  }
               ],
               "createdby" : {
                  "mail" : "Fred@home.com",
                  "objectguid" : "225566aa842f564da9751f9fc6dd0804",
                  "dn" : "CN=Fred Evans,OU=Utilities and Infrastructure,OU=Stafford,OU=FGUsers,DC=fg,DC=local",
                  "_id" : "5df9ce7e8d8958381a000029",
                  "cn" : "Fred Evans",
                  "samaccountname" : "fevans"
               },
               "date_create" : "2024-06-24T11:00:00.914Z",
               "stats" : {
                  "excluded" : 182,
                  "absence" : 0,
                  "sent" : 669
               },
               "coi_ref" : "COI-0068902",
               "instruction" : {
                  "mail" : "James@home.com",
                  "objectguid" : "976ab133fbd76446bcafeeb975e709ca",
                  "dn" : "CN=James Bond,OU=Rural,OU=Harborough,OU=FGUsers,DC=fg,DC=local",
                  "_id" : "60f56af71c1667f7aac2b72c",
                  "cn" : "James Bond",
                  "samaccountname" : "jbond"
               },
               "client" : {
                  "address" : "2 St Johns Street, Coventry ",
                  "name" : "Severn Trent Water Limited",
                  "postcode" : "CV1 2LZ ",
                  "type" : "Public or Private Company"
               },
               "natureInstruction" : "STW - Water Pipe."
            },
            "sort" : [ "2024-06-24T11:00:00.914Z" ]

Best answer by danilo_fme

Hi @bubblebeb 

 

I configurated the transformer JsonFragmenter:

json["_source"]["thirdparty"][*]

 

Next step is concatenate the result: Name.

 

Attached the workspace.

 

Thanks in Advance,

Danilo

 

 

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

1 reply

danilo_fme
Evangelist
Forum|alt.badge.img+44
  • Evangelist
  • Best Answer
  • June 28, 2024

Hi @bubblebeb 

 

I configurated the transformer JsonFragmenter:

json["_source"]["thirdparty"][*]

 

Next step is concatenate the result: Name.

 

Attached the workspace.

 

Thanks in Advance,

Danilo

 

 


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