LINQ to JSON - Query for object or an array

Question

I'm trying to get a list of SEDOL's & ADP values. Below is my json text:

{
    "DataFeed" : {
        "@FeedName" : "AdminData",
        "Issuer" : [{
                "id" : "1528",
                "name" : "ZYZ.A a Test Company",
                "clientCode" : "ZYZ.A",
                "securities" : {
                    "Security" : {
                        "id" : "1537",
                        "sedol" : "SEDOL111",
                        "coverage" : {
                            "Coverage" : [{
                                    "analyst" : {
                                        "@id" : "164",
                                        "@clientCode" : "SJ",
                                        "@firstName" : "Steve",
                                        "@lastName" : "Jobs",
                                        "@rank" : "1"
                                    }
                                }, {
                                    "analyst" : {
                                        "@id" : "261",
                                        "@clientCode" : "BG",
                                        "@firstName" : "Bill",
                                        "@lastName" : "Gates",
                                        "@rank" : "2"
                                    }
                                }
                            ]
                        },
                        "customFields" : {
                            "customField" : [{
                                    "@name" : "ADP Security Code",
                                    "@type" : "Textbox",
                                    "values" : {
                                        "value" : "ADPSC1111"
                                    }
                                }, {
                                    "@name" : "Top 10 - Select one or many",
                                    "@type" : "Dropdown, multiple choice",
                                    "values" : {
                                        "value" : ["Large Cap", "Cdn Small Cap", "Income"]
                                    }
                                }
                            ]
                        }
                    }
                }
            }, {
                "id" : "1519",
                "name" : "ZVV Test",
                "clientCode" : "ZVV=US",
                "securities" : {
                    "Security" : [{
                            "id" : "1522",
                            "sedol" : "SEDOL112",
                            "coverage" : {
                                "Coverage" : {
                                    "analyst" : {
                                        "@id" : "79",
                                        "@clientCode" : "MJ",
                                        "@firstName" : "Michael",
                                        "@lastName" : "Jordan",
                                        "@rank" : "1"
                                    }
                                }
                            },
                            "customFields" : {
                                "customField" : [{
                                        "@name" : "ADP Security Code",
                                        "@type" : "Textbox",
                                        "values" : {
                                            "value" : "ADPS1133"
                                        }
                                    }, {
                                        "@name" : "Top 10 - Select one or many",
                                        "@type" : "Dropdown, multiple choice",
                                        "values" : {
                                            "value" : ["Large Cap", "Cdn Small Cap", "Income"]
                                        }
                                    }
                                ]
                            }
                        }, {
                            "id" : "1542",
                            "sedol" : "SEDOL112",
                            "customFields" : {
                                "customField" : [{
                                        "@name" : "ADP Security Code",
                                        "@type" : "Textbox",
                                        "values" : {
                                            "value" : "ADPS1133"
                                        }
                                    }, {
                                        "@name" : "Top 10 - Select one or many",
                                        "@type" : "Dropdown, multiple choice",
                                        "values" : {
                                            "value" : ["Large Cap", "Cdn Small Cap", "Income"]
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            }
        ]
    }
}

Here's the code that I have so far:

var compInfo = feed["DataFeed"]["Issuer"]
.Select(p => new {  
    Id = p["id"],
    CompName = p["name"],
    SEDOL = p["securities"]["Security"].OfType<JArray>() ? 
        p["securities"]["Security"][0]["sedol"] : 
        p["securities"]["Security"]["sedol"]
    ADP = p["securities"]["Security"].OfType<JArray>() ? 
        p["securities"]["Security"][0]["customFields"]["customField"][0]["values"]["value"] : 
        p["securities"]["Security"]["customFields"]["customField"][0]["values"]["value"]
});

The error I get is:

Accessed JArray values with invalid key value: "sedol". Int32 array index expected

I think I'm really close to figuring this out. What should I do to fix the code? If there is an alternative to get the SEDOL and ADP value, please do let me know?

[UPDATE1] I've started working with dynamic ExpandoObject. Here's the code that I've used so far:

dynamic obj = JsonConvert.DeserializeObject<ExpandoObject>(json, new ExpandoObjectConverter());
foreach (dynamic element in obj)
{
    Console.WriteLine(element.DataFeed.Issuer[0].id);
    Console.WriteLine(element.DataFeed.Issuer[0].securities.Security.sedol);
    Console.ReadLine();
}

But I'm now getting the error 'ExpandoObject' does not contain a definition for 'DataFeed' and no extension method 'DataFeed' accepting a first argument of type 'ExpandoObject' could be found. NOTE: I understand that this json text is malformed. One instance has an array & the other is an object. I want the code to be agile enough to handle both instances.

[UPDATE2] Thanks to @dbc for helping me with my code so far. I've updated the json text above to closely match my current environment. I'm now able to get the SEDOLs & ADP codes. However, when I'm trying to get the 1st analyst, my code only works on objects and produces nulls for the analysts that are part of an array. Here's my current code:

var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
           let security = issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
           where security != null
           select new
           {
               Id = (string)issuer["id"], // Change to (string)issuer["id"] if id is not necessarily numeric.
               CompName = (string)issuer["name"],
               SEDOL = (string)security["sedol"],
               ADP = security["customFields"]
                .DescendantsAndSelf()
                .OfType<JObject>()
                .Where(o => (string)o["@name"] == "ADP Security Code")
                .Select(o => (string)o.SelectToken("values.value"))
                .FirstOrDefault(),
              Analyst = security["coverage"]
                .DescendantsAndSelf()
                .OfType<JObject>()
                .Select(jo => (string)jo.SelectToken("Coverage.analyst.@lastName"))
                .FirstOrDefault(),
           };

What do I need to change to always select the 1st analyst?

Answer 1

If you want all SEDOL & ADP values with the associated issuer Id and CompName for each, you can do:

var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
               from security in issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf())
               select new
               {
                   Id = (long)issuer["id"], // Change to (string)issuer["id"] if id is not necessarily numeric.
                   CompName = (string)issuer["name"],
                   SEDOL = (string)security["sedol"],
                   ADP = security["customFields"]
                    .DescendantsAndSelf()
                    .OfType<JObject>()
                    .Where(o => (string)o["@name"] == "ADP Security Code")
                    .Select(o => (string)o.SelectToken("values.value"))
                    .FirstOrDefault(),
               };

Using the extension methods:

public static class JsonExtensions
{
    public static IEnumerable<JToken> DescendantsAndSelf(this JToken node)
    {
        if (node == null)
            return Enumerable.Empty<JToken>();
        var container = node as JContainer;
        if (container != null)
            return container.DescendantsAndSelf();
        else
            return new[] { node };
    }

    public static IEnumerable<JObject> ObjectsOrSelf(this JToken root)
    {
        if (root is JObject)
            yield return (JObject)root;
        else if (root is JContainer)
            foreach (var item in ((JContainer)root).Children())
                foreach (var child in item.ObjectsOrSelf())
                    yield return child;
        else
            yield break;
    }
}

Then

Console.WriteLine(JsonConvert.SerializeObject(compInfo, Formatting.Indented));

Produces:

[
  {
    "Id": 1528,
    "CompName": "ZYZ.A a Test Company",
    "SEDOL": "SEDOL111",
    "ADP": "ADPSC1111"
  },
  {
    "Id": 1519,
    "CompName": "ZVV Test",
    "SEDOL": "SEDOL112",
    "ADP": "ADPS1133"
  },
  {
    "Id": 1519,
    "CompName": "ZVV Test",
    "SEDOL": "SEDOL112",
    "ADP": "ADPS1133"
  }
]

However, in the query you have written so far, you seem to be trying to return only the first SEDOL & ADP for each issuer. If that is really what you want, do:

var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
               let security = issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
               where security != null
               select new
               {
                   Id = (long)issuer["id"], // Change to (string)issuer["id"] if id is not necessarily numeric.
                   CompName = (string)issuer["name"],
                   SEDOL = (string)security["sedol"],
                   ADP = security["customFields"]
                    .DescendantsAndSelf()
                    .OfType<JObject>()
                    .Where(o => (string)o["@name"] == "ADP Security Code")
                    .Select(o => (string)o.SelectToken("values.value"))
                    .FirstOrDefault(),
               };

Which results in:

[
  {
    "Id": 1528,
    "CompName": "ZYZ.A a Test Company",
    "SEDOL": "SEDOL111",
    "ADP": "ADPSC1111"
  },
  {
    "Id": 1519,
    "CompName": "ZVV Test",
    "SEDOL": "SEDOL112",
    "ADP": "ADPS1133"
  }
]

As an aside, since your JSON is rather polymorphic (properties are sometimes arrays of objects and sometimes just objects) I don't think deserializing to a class hierarchy or ExpandoObject will be easier.

Update

Given your updated JSON, you can use SelectTokens() with the JSONPath recursive search operator .. to find the first analyst's last name, where the recursive search operator handles the fact that the analysts might or might not be contained in an array:

var compInfo = from issuer in feed.SelectTokens("DataFeed.Issuer").SelectMany(i => i.ObjectsOrSelf())
               let security = issuer.SelectTokens("securities.Security").SelectMany(s => s.ObjectsOrSelf()).FirstOrDefault()
               where security != null
               select new
               {
                   Id = (string)issuer["id"], // Change to (string)issuer["id"] if id is not necessarily numeric.
                   CompName = (string)issuer["name"],
                   SEDOL = (string)security["sedol"],
                   ADP = (string)security["customFields"]
                    .DescendantsAndSelf()
                    .OfType<JObject>()
                    .Where(o => (string)o["@name"] == "ADP Security Code")
                    .Select(o => o.SelectToken("values.value"))
                    .FirstOrDefault(),
                   Analyst = (string)security.SelectTokens("coverage.Coverage..analyst.@lastName").FirstOrDefault(),
               };