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 https://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, Mar 31 '16 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, Mar 31 '16 at 18:10

Brian Rogers · Accepted Answer · 2016-03-31T18:46:56.077

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

score 1 · Answer 2 · answered Mar 31 '16 at 18:41

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"],
    };

LINQ to JSON - Querying an array

2 Answers2