Filter JSON Array with dynamic conditions

Question

I have many JSON array with different types of nodes in it.

Sample Json 1:

[
    {
          "EmpID": "23",
          "EmpName": "Jhon",
          "Age": "23"
    },
    {
          "EmpID": "29",
          "EmpName": "Paul",
          "Age": "25"
    },
    {
          "EmpID": "123",
          "EmpName": "Jack",
          "Age": "29"
    },
    {
          "EmpID": "129",
          "EmpName": "Apr",
          "Age": "29"
    }
]

Sample Json 2

[
    {
          "DepID": "2",
          "Name": "Sales"
    },
    {
          "DepID": "5",
          "Name": "Marketing"
    },
    {
          "DepID": "12",
           "Name": "IT"
    }
]

I want to filter them based on different conditions such as

1)EmpID=29

This should return

[
    {
          "EmpID": "29",
           "EmpName": "Paul",
           "Age": "25",
    }
]

2)Age=23 and EmpName=Jhon

This should return

[
    {
          "EmpID": "23",
           "EmpName": "Jhon",
           "Age": "23"
    }
]

3. Age=29

This should return

[
    {
          "EmpID": "123",
           "EmpName": "Jack",
           "Age": "29"
    },
    {
          "EmpID": "129",
           "EmpName": "Apr",
           "Age": "29"
    }
]

So I need a generic approach to do any number of filters on the JSON array. I am planning to get all the filters using some comma separated string like Age="23",EmpName="Jhon" and this can be converted to any format in the code.

I have tried creating dynamic filter using Json Path such as $.[?(@.Age == '23' && @.EmpName == 'Jhon')].

Also I tried using LINQ like

var result = JsonConvert.DeserializeObject(jsonString);
var res = (result as Newtonsoft.Json.Linq.JArray).Where(x =>
           x["Age"].ToString() =="23" && x["EmpName"].ToString()=="Jhon").ToList(); 

But how I can generate the where conditions dynamically based on any number of conditions I receive Also there is a plan to include Date filters in case there is some datetime nodes in json such as BirthDate>12051995. I am not sure how I can dynamically filter using any number of input filter conditions.

Answer 1

You have almost nailed it. :)

1. Instead of using DeserializeObject and then converting it to JArray prefer JArray.Parse

var json = File.ReadAllText("sample.json");
var semiParsedJson = JArray.Parse(json);

2. Instead of using ToList after Where prefer JArray constructor which can work well with an IEnumerable<JToken>

const string IdField = "EmpID", NameField = "EmpName", AgeField = "Age";
const StringComparison caseIgnorant = StringComparison.OrdinalIgnoreCase;

var idEq29 = semiParsedJson.Children()
    .Where(token => string.Equals(token[IdField].Value<string>(),"29", caseIgnorant));

Console.WriteLine(new JArray(idEq29).ToString());

3. The other queries can be implemented in the very same way

var ageEq23AndNameJhon = semiParsedJson.Children()
    .Where(token => string.Equals(token[AgeField].Value<string>(), "23", caseIgnorant)
                    && string.Equals(token[NameField].Value<string>(), "Jhon", caseIgnorant));

Console.WriteLine(new JArray(ageEq23AndNameJhon).ToString());

var ageEq29 = semiParsedJson.Children()
    .Where(token => string.Equals(token[AgeField].Value<string>(), "29", caseIgnorant));

Console.WriteLine(new JArray(ageEq29).ToString());

---

UPDATE #1: Enhance proposed solution With the following extension method

public static class JArrayExtensions
{
    public static JArray Filter(this JArray array, Func<JToken, bool> predicate)
        => new JArray(array.Children().Where(predicate));
}

you can greatly simplify the filtering

var idEq29 = semiParsedJson
    .Filter(token => string.Equals(token[IdField].Value<string>(),"29", caseIgnorant));

var ageEq23AndNameJhon = semiParsedJson
    .Filter(token => string.Equals(token[AgeField].Value<string>(), "23", caseIgnorant))
    .Filter(token => string.Equals(token[NameField].Value<string>(), "Jhon", caseIgnorant));

var ageEq29 = semiParsedJson
    .Filter(token => string.Equals(token[AgeField].Value<string>(), "29", caseIgnorant));

Console.WriteLine(idEq29);
Console.WriteLine();
Console.WriteLine(ageEq23AndNameJhon);
Console.WriteLine();
Console.WriteLine(ageEq29);

---

Or you can push it even further. If all the fields store string values then you can define the extension method like this:

public static class JArrayExtensions
{
    public static JArray Filter(this JArray array, string field, string value)
        => new JArray(array.Children().Where(GenerateFilter(field, value)));

    private static Func<JToken, bool> GenerateFilter(string field, string value)
        => (JToken token) => string.Equals(token[field].Value<string>(), value, StringComparison.OrdinalIgnoreCase);
}

The the filter queries are super simple :D

var idEq29 = semiParsedJson
    .Filter(IdField,"29");

var ageEq23AndNameJhon = semiParsedJson
    .Filter(AgeField, "23")
    .Filter(NameField, "Jhon");

var ageEq29 = semiParsedJson
    .Filter(AgeField, "29");

Console.WriteLine(ageEq23AndNameJhon);
Console.WriteLine();

Console.WriteLine(idEq29);
Console.WriteLine();

Console.WriteLine(ageEq29);

Answer 2

To get this working in a traditional way, you'll need to perform 3 steps:

• define a class to contain the data
• deserialize the json into a list of objects
• use linq to query your selection

You can do the same thing for the departments. If you need to join them in any way, use .Join. If the JSON is mixed, you can create a single class containing all the properties and use that to query.

---

So for the simple case: first define a class to represent you object:

public class Employee
{
    public int EmpID {get;set;}
    public string EmpName {get;set;}
    public int Age {get;set;}
}

Then deserialize and query:

put at the top:

using System.Text.Json;

public void Main()
{
     //deserialize into a list
     List<Employee> employees = 
                JsonSerializer.Deserialize<List<Employee>>(yourJsonString); 

     //query
     var result = employees.Where(c => c.Age == 23 && c.EmpName == "Jhon");
     
     //show results
     foreach (var employee in result)
         Console.WriteLine(employee.EmpID);
}

---

As by update:

Depending on your use case you have a couple of options:

• a fixed number of dynamic properties
• a truly dynamic query

A fixed number of dynamic properties

You can achieve a more dynamic setup with the following:

//define the filterable properties
//note they are nullable
int? age = null;
int? id = null;
string name = null;

//apply them in a query
//
//note: if one of the filter properties is not set, 
//      that side of the && expression evaluates to "true"
var result = employees.Where(c => (age == null ? true : c.Age == age) && 
                                  (id == null ? true : c.EmpId == id) &&
                                  (name == null ? true : c.EmpName == name));

a truly dynamic query

Now here things start to get tricky. One possible option is to generate a string based query, with the help of a libary like Dynamic Linq