Extract objects from JSON with nested JSON arrays

[jacchr]

Hi Experts

I have a JSON response looking like this:

{
    "auditIdg": "cjmifeoijje-jfddsof743-sfjofe837632-dj733i4",
    "familyTree": {
        "cId": 1234567,
        "fullBusinessName": "AJAX INC",
        "affiliateType": "GUP",
        "duns": "012345678",
        "entities": [
            {
                "entity": {
                    "cId": 1111111,
                    "fullBusinessName": "AJAX LTD",
                    "affiliateType": "CS",
                    "duns": "010101010",
                    "entities": [
                        {
                            "entity": {
                                "cId": 2222222,
                                "fullBusinessName": "AJAX Holding",
                                "affiliateType": "CS",
                                "duns": "020202020"
                            }
                        }
                    ]
                }
            },
            {
                "entity": {
                    "cId": 3333333,
                    "fullBusinessName": "Ajax Corp",
                    "affiliateType": "CS",
                    "duns": "030303030"
                }
            },
            {
                "entity": {
                    "cId": 4444444,
                    "fullBusinessName": "AJAX (ES) LTD",
                    "affiliateType": "CS",
                    "duns": "040404040"
                }
            },
            {
                "entity": {
                    "cId": 5555555,
                    "fullBusinessName": "AJAX AB",
                    "affiliateType": "CS",
                    "duns": "050505050",
                    "entities": [
                        {
                            "entity": {
                                "cId": 6666666,
                                "fullBusinessName": "AJAX (SE) AB",
                                "affiliateType": "CS",
                                "duns": "060606060"
                            }
                        },
                        {
                            "entity": {
                                "cId": 777777,
                                "fullBusinessName": "Ajax Holding (SE) AB",
                                "affiliateType": "CS",
                                "duns": "070707070"
                            }
                        },
                        {
                            "entity": {
                                "cId": 8888888,
                                "fullBusinessName": "Ajax Super AB",
                                "affiliateType": "CS",
                                "duns": "080808080"
                            }
                        }
                    ]
                }
            },
            {
                "entity": {
                    "cId": 9999999,
                    "fullBusinessName": "Ajax Sub (AUS)",
                    "affiliateType": "CS",
                    "duns": "090909090"
                }
            }
        ]
    }
}

I need to extract all fullBusinessName each entity into an array/list of strings. But each entity can contain a sub JSON array with one or more entitites - which again also can contain one or more entities, etc.

How do I extract the required entries? One workaround is to use RegEx but can it not be done with the JSON object itself?

[ppr (Peter)]

Serialize JSON activity - output myJObject

[ppr (Peter)]

Also have a look here:

[jacchr]

Thanks @ppr it was just what I needed - very much appreciated

[jacchr]

Hi @ppr

The solution in outputting to a data table is really great - however I noticed that it skips the first entry (most likely to the filtering out of “entities”).

How do I include the first entity with "cId": 1234567 and "fullBusinessName": "AJAX INC" as well?

[ppr (Peter)]

@jacchr

How do I include the first entity with

add it seperatly similar like the entity

[jacchr]

So make multiple Assign activities, or?

[ppr (Peter)]

assign Activity

JOList | List(Of JObject) =

(From f In {"..familyTree","..entity"}
From jo In myJObject.SelectTokens(f).Cast(Of JObject)
Let pl = jo.Properties.Where(Function (p) Not p.Name.Equals("entities"))
Select jor = New JObject(pl)).toList

Assign Activity
dtResult = JArray.FromObject(JOList).toObject(Of DataTable)

[jacchr]

Thanks @ppr that was very helpful