Proper syntax for LINQ to JSON query with nested arrays

Question

I have a Json “database” of sorts, and am trying to extract contents from it using Newtonsoft Json.Net LINQ.

The goal is to extract a property from the items in a child array contained within an item in a parent array. The item in the parent array will be selected by a key property. The “outer” element is an array with one entry, the “inner” element is an array of two parent entries, each of which has a “key” and an array of child “items.” Looking to match the inner parent item by its key, and then extract the child “itemid” properties of all items in the child array into an IList.

file at filePath contains:
{
   "outer":
   [
      {
         "inner":
         [
            {
               "key": "AAA",
               "items":
               [
                  { "itemid": "a1" }
               ]
            },
            {
               "key": "BBB",
               "items":
               [
                  { "itemid": "b1" },
                  { "itemid": "b2" }
               ]
            }
         ]
      }
   ]
}

I’m thinking the extraction query would look something like what is shown below, but neither this nor any of the dozens of variations of this I’ve tried have been successful. In some variations it seems to work all the way up to the final selection of the correct inner “items” but then falls apart on me when trying to dig out the array of “itemid.” Any clues what is wrong here? I’m willing to split this up into two separate queries if need be.

public IList<string> FindMatchingItems(string filePath, string id)
{
    JObject JsonLinq = JObject.Parse(File.ReadAllText(filePath));
    return JsonLinq["outer"].First()["inner"].Children()
        .First(inner => inner["key"].ToString() == id)
        .Select(selected => selected["items"].Children()["itemid"].ToString())
        .ToList();
}

void Query(string filePath)
{
    var ayes = FindMatchingItems(filePath, "AAA");
    // ayes contains [] { "a1" }

    var bees = FindMatchingItems(filePath, "BBB");
    // bees contains [] { "b1", "b2" }
}

Andrew Whitaker · Accepted Answer · 2014-09-10 19:41:25Z

2

I think this is what you're looking for:

JsonLinq["outer"].First()["inner"]
    .Where(innerItem => innerItem["key"].Value<string>() == id)
    .SelectMany(innerItem => innerItem["items"]
        .Select(itm => itm["itemid"].Value<string>()))
    .ToList();

The key here is to use .SelectMany to flatten the projected itemids into a new list.

edited Sep 10, 2014 at 19:41

answered Sep 10, 2014 at 19:30

Andrew Whitaker

126k32 gold badges296 silver badges308 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian Kretzler Over a year ago

Perfect, thanks! I'd ignored SelectMany in every attempt

Andrew Whitaker Over a year ago

@BrianKretzler: No problem. I'm still working through why the call to Select was causing an outright error (it is for me anyway).

Valentin · Accepted Answer · 2014-09-10 19:18:29Z

1

You can create a class in C# and then use JsonConvert.DeserializeObject()

C# class
public class Item
{
    public string itemid { get; set; }
}

public class Inner
{
    public string key { get; set; }
    public List<Item> items { get; set; }
}

public class Outer
{
public List<Inner> inner { get; set; }
}

public class RootObject
{
    public List<Outer> outer { get; set; }
 }

And then you can use

    public List<string> FindMatchingItems(string filePath, string id)
    {
        var json = File.ReadAllText(filePath);
        var rootObject = JsonConvert.DeserializeObject<RootObject>(json);
        var result = rootObject.outer.First().inner.First(i => i.key == id).items.Select(item => item.itemid).ToList();

        return result;
    }

edited Sep 10, 2014 at 19:18

answered Sep 10, 2014 at 18:54

Valentin

4031 gold badge5 silver badges10 bronze badges

2 Comments

Brian Kretzler Over a year ago

thank you so much for this suggestion. While this may be a good implementation choice, what I'm struggling with is the LINQ query syntax. I'd really like to learn what is wrong with my attempts at using a LINQ query first.

Valentin Over a year ago

The json linq looks ugly. I think its more easy to work with linq for objects. Here is a nice place from where you can learn linq code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

Collectives™ on Stack Overflow

Proper syntax for LINQ to JSON query with nested arrays

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related