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?

I would deserialize the json into a dynamic ExpandoObject (for brevity, otherwise define a type) then use linq. — Wjdavis5, Apr 13 '16 at 15:05
Could you provide some code on how I can do this with a dynamic ExpandoObject? — inquisitive_one, Apr 13 '16 at 17:37
Again here, you need to debug the code. Set a breakpoint inside your foreach loop and inspect / expand the element variable. What values exist in side of it? Is there a DataFeed element inside of that object? Use your debugger, observe the contents of the entire expandoobject. — Wjdavis5, Apr 13 '16 at 19:21
I'm using LINQPAD as my IDE. I see element > Key "DataFeed", Value "ExpandoObject". Then it drills down to more key value pairs. I'm not sure how to post a image through this. As far as I can drill down, I can see the SEDOLs & ADP codes. Am I referencing the value wrong? — inquisitive_one, Apr 13 '16 at 20:41

dbc · Accepted Answer · 2016-04-17T23:36:25.113

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(),
               };

Thanks. That works. Question: would you explain the significance of `..`? — inquisitive_one, Apr 17 '16 at 19:22
@inquisitive_one - `..` is the JSONPath recursive search operator. I.e. `"a..b"` matches any token in the JSON hierarchy whose topmost property is named `"a"` and bottom most is named `"b"`, with any number of arrays and objects inbetween - or none at all. It handles the polymorphism you are seeing in your JSON where sometimes data is nested in an array, and sometimes not. For more on JSONPath see the article linked in the answer. — dbc, Apr 17 '16 at 23:35

Bruno Garcia · Answer 2 · 2016-04-14T18:11:29.367

Also note your JSON is malformed.

The proxy class created can't figure out the type of Security because in one instance it's an array, and in another it's a simple object.

Original answer:

I used json2csharp to help me get some classes, and now I can use the types:

        var obj = JsonConvert.DeserializeObject<RootObject>(json);
        var result = from a in obj.DataFeed.Issuer
                     select new
                     {
                         Sedol = a.securities.Security.sedol,
                         Name = a.name
                     };

Classes:

     public class Values
    {
        public object value { get; set; }
    }

    public class CustomField
    {
        public string name { get; set; }
        public string type { get; set; }
        public Values values { get; set; }
    }

    public class CustomFields
    {
        public List<CustomField> customField { get; set; }
    }

    public class Security
    {
        public string id { get; set; }
        public string sedol { get; set; }
        public CustomFields customFields { get; set; }
    }

    public class Securities
    {
        public Security Security { get; set; }
    }
    public class Issuer
    {
        public string id { get; set; }
        public string name { get; set; }
        public string clientCode { get; set; }
        public Securities securities { get; set; }
    }

    public class DataFeed
    {
        public string FeedName { get; set; }
        public List<Issuer> Issuer { get; set; }
    }

    public class RootObject
    {
        public DataFeed DataFeed { get; set; }
    }

That's the json text that is given to me. That's my dilemma and that was what I was trying to do with my LINQ code. If it finds a type of array, then get the values from the 1st array, otherwise just get the values. — inquisitive_one, Apr 13 '16 at 18:12

Wjdavis5 · Answer 3 · 2016-04-13T18:02:21.940

0

Using newtonsoft.json

dynamic obj = JsonConvert.DeserializeObject<ExpandoObject>(json);

I dont have my ide in front of me but the value should be at:

obj.DataFeed.Issuer[0].securities.Security.sedol

edited Apr 13 '16 at 18:02

answered Apr 13 '16 at 17:40

Wjdavis5

3,952
7
35
63

I tried that already, but how would you get the SEDOL or the ADP value? – inquisitive_one Apr 13 '16 at 17:46
have you inspected the resulting object in the debugger? The object's hierarchy will mimic that of your json object – Wjdavis5 Apr 13 '16 at 18:00
I used this deserializer command: `var obj = JsonConvert.DeserializeObject(json, new ExpandoObjectConverter());`. I then get this error when trying to find sedol or even the name. `'ExpandoObject' does not contain a definition for 'DataFeed' and no extension method 'DataFeed' accepting a first argument of type 'ExpandoObject' could be found`. – inquisitive_one Apr 13 '16 at 18:24
1

Try dynamic instead of var – Wjdavis5 Apr 13 '16 at 18:27
One of the neat things about this method is that the resulting ExpandoObject is really a dictionary underneath (you can cast it to dictionary and back). This would allow you to easily foreach the object looking for the key name you need. – Wjdavis5 Apr 13 '16 at 18:35
Ok. Changed it to dynamic (instead of var) and tried this code: `foreach (var element in obj) { Console.WriteLine(element.DataFeed.Issuer[0].id); }`. I'm still getting the error I mentioned in my last comment. Please let me know if I'm doing this wrong. – inquisitive_one Apr 13 '16 at 18:44
1

`foreach(dynamic element in obj)` you are using var again. Further I'll need to see the full code, maybe update your question with the example. – Wjdavis5 Apr 13 '16 at 19:01

LINQ to JSON - Query for object or an array

3 Answers3