Json - Filtering using Newtonsoft with multiple conditions in C#

BezqyczekPL
June 17, 2024
164 views
1 vote
2 Answers

I’m trying to extract values from json using Newtonsoft, but I’m having some problems.
Below is the json snippet I use:

{
    "result": {
        "areas": [
            {
                "area": 1,
                "items": [
                    {
                        "name": "name1",
                        "numbers": [
                            2,
                            2,
                            2,
                            1
                        ],
                        "value": 0.3
                    },
                    {
                        "name": "name2",
                        "numbers": [
                            0,
                            1,
                            0,
                            1
                        ],
                        "value": 0.6
                    },
                    {
                        "name": "name3",
                        "numbers": [
                            -2,
                            3,
                            1,
                            1
                        ],
                        "value": 0.5
                    }
                ]
            },
            {
                "area": 2,
                "items": [
                    {
                        "name": "name4",
                        "numbers": [
                            1,
                            2,
                            3,
                            4
                        ],
                        "value": 0.7
                    }
                ]
            }
        ]
    }
}

My task is to first filter by area (I need data only from area 1) and then select those items that meet the conditions for numbers:

1st number = 2 or = -2,
2nd number > 1,
3rd number > 0,
4th number = 1,

and then calculate the average value for these selected items.

In this case, I want to select items 1 and 3, i.e. to get an average of 0.4.

I first tried to do it in one filter with multiple conditions:

IEnumerable<JToken> numbers = jo.SelectTokens("$...items[?( (@.numbers[0] == 2 || @.numbers[0] == -2 ) && @.numbers[1] > 1 && @.numbers[2] > 0 && @.numbers[3] == 1)].value");

I got an error in this case because I can’t use additional brackets in the query, and I don’t know how else to check all the conditions.
Then I wrote each query separately and connected them using the Intersect() function:

IEnumerable<JToken> numbers0 = jo.SelectTokens("$...items[?(@.numbers[0] == 2 || @.numbers[0] == -2)]");
IEnumerable<JToken> numbers1 = jo.SelectTokens("$...items[?(@.numbers[1] > 1)]");
IEnumerable<JToken> numbers2 = jo.SelectTokens("$...items[?(@.numbers[2] > 0)]");
IEnumerable<JToken> numbers3 = jo.SelectTokens("$...items[?(@.numbers[3] == 1)]");

IEnumerable<JToken> numbers = numbers0.Intersect(numbers1.Intersect(numbers2.Intersect(numbers3)));

In this case, however, I am not able to extract the value and the entire json fragment is returned to me.

In both cases, I am unable to include the area filtering condition, the returned token is empty:

IEnumerable<JToken> area = jo.SelectTokens("$..areas[?( @.area == 1)]");

Am I doing something wrong? Maybe this can be fixed somehow without changing the approach.
And if this is not possible, what technique do you recommend? I’ve read a bit about LINQ and will try that next if the current task is unsolvable, unless you think there’s something even better.

Tags: c#json json.net jsonpath

Answers

- dbc
- June 13, 2024 at 5:00 pm
- 0 votes
0
You are correct that SelectTokens() does not support parenthesized combinations of AND and OR, see How can I create a JSONPath filter expression containing both AND and OR operators? for confirmation. Thus the three possible workarounds from this answer for filtering the "$...items[*]" items should work for you as well. Once you have filtered the items as required, you can append a .Select(i => i["value"]) to your query to select the value you need.

Specifically, you have the following three ways to work around Json.NET’s limitation and select the values you need:
1. You can rely on Json.NET’s undocumented behavior that A && B || C means A && (B || C):
```
var values = jo.SelectTokens("$...items[?(@.numbers[1] > 1 && @.numbers[2] > 0 && @.numbers[3] == 1 && @.numbers[0] == 2 || @.numbers[0] == -2)].value");
```
  No added Select() is required with this approach.
2. You can use Intersect() as you are doing now, with an added Select():
```
var items0 = jo.SelectTokens("$...items[?(@.numbers[0] == 2 || @.numbers[0] == -2)]");
var items1 = jo.SelectTokens("$...items[?(@.numbers[1] > 1 && @.numbers[2] > 0 && @.numbers[3] == 1)]");

var values = items0.Intersect(items1).Select(i => i["value"]);
```
3. You could use LINQ to filter your items instead of JSONPath, with the added select:
```
var values = 
    from i in jo.SelectTokens("$...items[*]")
    let numbers = i["numbers"]
    where ((double?)numbers?[0] == 2 || (double?)numbers?[0] == -2) 
        && (double?)numbers?[1] > 1 
        && (double?)numbers?[2] > 0 
        && (double?)numbers?[3] == 1
    select i["value"];
```
  This approach is, in my opinion, the most readable.
Once you have your values, you can average them by casting each selected JToken to a double? like so
```
var average = values.Average(v => (double?)v);
```
Demo fiddle showing all three approaches here.
Login or Signup to reply.

I will try to solve this sentence by sentence with minimal coding.

1/. "I’m trying to extract values from json using Newtonsoft"
-> https://www.newtonsoft.com/json/help/html/deserializeobject.htm

2/. How to generate class from Json
https://app.quicktype.io

After those step: the code boils down to : live demo

using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;

public static void Main()
{
  var result = JsonConvert.DeserializeObject<SoNumbers>(GetJson());
}

public static string GetJson()  {return "inputstring"; }

public partial class SoNumbers
{
  [JsonProperty("result")]
  public Result Result { get; set; }
}

public partial class Result
{
  [JsonProperty("areas")]
  public List<Area> Areas { get; set; }
}

public partial class Area
{
  [JsonProperty("area")]
  public long Id { get; set; }

  [JsonProperty("items")]
  public List<Item> Items { get; set; }
}

public partial class Item
{
  [JsonProperty("name")]
  public string Name { get; set; }

  [JsonProperty("numbers")]
  public List<int> Numbers { get; set; }

  [JsonProperty("value")]
  public double Value { get; set; }
}

3/. "filter by area 1"

result.Result.Areas.Where(x=> x.Id == 1)

4/. Valid item based on Numbers indexes and value.

static bool IsItemValid(Item item)
{
    return 
        (item.Numbers[0] == 2 || item.Numbers[0] == -2) 
      && item.Numbers[1] > 1
      && item.Numbers[2] > 0
      && item.Numbers[3] == 1;
}

5/. Use it to validation to get Item 1 and 3.

var validItems = area.Items.Where(IsItemValid);

6/. Average the value.

var avg = validItems.Average(x=> x.Value);

final result : livedemo

var result = JsonConvert.DeserializeObject<SoNumbers>(GetJson());
var area1 = result.Result.Areas.FirstOrDefault(x=> x.Id == 1);
var validItems = area1.Items.Where(IsItemValid);
var avg = validItems.Average(x=> x.Value);

Console.WriteLine($"Area n°{area1.Id}, ItemSelected = [{string.join(",",validItems.Select(x=> x.Name))}] Average = {avg};");

Please signup or login to give your own answer.

Click here to cancel reply.

Json – Filtering using Newtonsoft with multiple conditions in C#

Answers