LINQ to JSON - Querying an array

Question

I need to select users that have a "3" in their json array.

{
    "People":[  
    {  
        "id" : "123",
        "firstName" : "Bill",
        "lastName" : "Gates",
        "roleIds" : {
                "int" : ["3", "9", "1"]
            }
    },
    {  
        "id" : "456",
        "firstName" : "Steve",
        "lastName" : "Jobs",
        "roleIds" : {
            "int" : ["3", "1"]
        }
    },
    {  
        "id" : "789",
        "firstName" : "Elon",
        "lastName" : "Musk",
        "roleIds" : {
            "int" : ["3", "7"]
        }
    },
    {
        "id" : "012",
        "firstName" : "Agatha",
        "lastName" : "Christie",
        "roleIds" : {
            "int" : "2"
        }
    }
]}

In the end, my results should be Elon Musk & Steve Jobs. This is the code that I used (& other variations):

var roleIds = pplFeed["People"]["roleIds"].Children()["int"].Values<string>();


var resAnAssocInfo = pplFeed["People"]
.Where(p => p["roleIds"].Children()["int"].Values<string>().Contains("3"))
.Select(p => new
{
    id = p["id"],
    FName = p["firstName"],
    LName = p["lastName"]
}).ToList();

I'm getting the following error:

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

I changed .Values<string>() to .Values<int>() and still no luck.

What am I doing wrong?

Your JSON is invalid. Upload it to jsonformatter.curiousconcept.com to see the problem. Possibly {"People": [{ { "id" : "123" ... should be {"People": [{ "id" : "123" ... , with the matching } also removed from the end? — dbc
– dbc, Commented Mar 31, 2016 at 16:00
Good eye. I've updated the json to follow the correct format. However, it still doesn't work. Any idea what I'm doing wrong? — inquisitive_one
– inquisitive_one, Commented Mar 31, 2016 at 18:10

Brian Rogers · Accepted Answer · 2016-03-31 18:46:56Z

You are pretty close. Change your Where clause from this:

.Where(p => p["roleIds"].Children()["int"].Values<string>().Contains("3"))

to this:

.Where(p => p["roleIds"]["int"].Children().Contains("3"))

and you will get you the result you want (although there are actually three users in your sample data with a role id of "3", not two).

However, there's another issue that you might hit for which this code still won't work. You'll notice that for Agatha Christie, the value of int is not an array like the others, it is a simple string. If the value will sometimes be an array and sometimes not, then you need a where clause that can handle both. Something like this should work:

.Where(p => p["roleIds"]["int"].Children().Contains(roleId) ||
            p["roleIds"]["int"].ToString() == roleId)

...where roleId is a string containing the id you are looking for.

Fiddle: https://dotnetfiddle.net/Zr1b6R

Jeff Mercado · Accepted Answer · 2016-03-31 18:41:24Z

The problem is that not all objects follow the same interface. The last item in that list has a single string value in the roleIds.int property while all others has an array. You need to normalize that property and then do the check. It'll be easiest if they were all arrays.

You should be able to do this:

var roleId = "3";
var query =
    from p in pplFeed["People"]
    let roleIds = p.SelectToken("roleIds.int")
    let normalized = roleIds.Type == JTokenType.Array ? roleIds : new JArray(roleIds)
    where normalized.Values().Contains(roleId)
    select new
    {
        id = p["id"],
        FName = p["firstName"],
        LName = p["lastName"],
    };

Collectives™ on Stack Overflow

LINQ to JSON - Querying an array

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related