Filtering Complex JSON by dynamic filter query in c#

Question

I am trying to create a JSON filtering in one of my projects and here JSON will be dynamic so can not create a Model and filter will come from user, my sample JSON is

[
  {
    "id": 101,
    "field1": "f1",
    "field2": "f2",
    "InnerArray": [
      {
        "id": 201,
        "innerField1": "f1",
        "innerField2": "f2"
      },
      {
        "id": 202,
        "innerField1": "f1",
        "innerField2": "f2"
      }
    ]
  },
  {
    "id": 102,
    "field1": "ff1",
    "field2": "ff2",
    "InnerArray": [
      {
        "id": 301,
        "innerField1": "f1",
        "innerField2": "f2"
      },
      {
        "id": 302,
        "innerField1": "f1",
        "innerField2": "f2"
      }
    ]
  }
]

I am trying to filter this by SelectToken() and which will work fine except for inner array for example if the query is

string filter = "$.[?(@.id==101)]";
JToken filteredData = data.SelectToken($"{filter}");

//We will get
{
  "id": 101,
  "field1": "f1",
  "field2": "f2",
  "InnerArray": [
    {
      "id": 201,
      "innerField1": "f1",
      "innerField2": "f2"
    },
    {
      "id": 202,
      "innerField1": "f1",
      "innerField2": "f2"
    }
  ]
}

but if I want to filter the JSOn by inner array element then it will not work

string filter = "$.[?(@.InnerArray[?(@.id==301)])]";
JToken filteredData = data.SelectToken($"{filter}");

//Result is 
{
  "id": 102,
  "field1": "ff1",
  "field2": "ff2",
  "InnerArray": [
    {
      "id": 301,
      "innerField1": "f1",
      "innerField2": "f2"
    },
    {
      "id": 302,
      "innerField1": "f1",
      "innerField2": "f2"
    }
  ]
}

My expectation is

{
  "id": 102,
  "field1": "ff1",
  "field2": "ff2",
  "InnerArray": [
    {
      "id": 301,
      "innerField1": "f1",
      "innerField2": "f2"
    }
  ]
}

InnerArray Filter returning all elements and inner JSON PATH not taking, is there any alternative way to define JSON Path? or any alternative is there to dynamically filter JSON since here JSON will be dynamic and the filter will be dynamic

Davey van Tilburg · Accepted Answer · 2020-04-21 16:45:14Z

2

It is possible, I've constructed the following executable code to do so:

To make it parsable, otherwise JToken.Parse says that json cannot start as an array.

string sourceFile = File.ReadAllText("./source.json");
JToken source = JToken.Parse(sourceFile);

List<JToken> tokensToRemove = source.SelectTokens("$..*[?(@.id == 101 || @.id == 301)]").ToList();

tokensToRemove.ForEach(t => t.Remove());

string result = source.ToString();

Result will hold what you said you were expecting.

FYI, $.. selects all elements from parent regardless of how deep.

--- EDIT:

For the follow up question about doing the reverse. That is possible, but you'd have to approach it a bit differently. Since the items are in different levels of the source object, I think its best if you construct a new JObject with an array of the results you want to have.

Like so:

string sourceFile = File.ReadAllText("./source.json");
JToken source = JToken.Parse(sourceFile);

List<JToken> tokensToKeep = source.SelectTokens("$..*[?(@.id == 101 || @.id == 301)]").ToList();

JObject resultObject = new JObject();
JArray array = new JArray();
resultObject.Add("array", array);
tokensToKeep.ForEach(t => array.Add(t));

string result = resultObject.ToString();

edited Apr 21, 2020 at 16:45

answered Apr 21, 2020 at 11:42

Davey van Tilburg

7084 silver badges20 bronze badges

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

5 Comments

weichch Over a year ago

Try JToken.Parse :)

Davey van Tilburg Over a year ago

@weichch Ah ofcourse headdesks JObject requires the first item to be a ... object :P Thanks for the heads up

Nithya Over a year ago

@Davey van Tilburg Thank you I think this should work, reverse of this is possible? I mean keeping all matching conditions and removing others?

Davey van Tilburg Over a year ago

@Nithya I don't know if this is what you mean, but see my updated answer. And please :) if this is the answer you look for, i'd very much appreciate it if you would mark it as the answer.

Nithya Over a year ago

@Davey van Tilburg Thank you for the help and yes this is answers the question

weichch · Accepted Answer · 2020-04-21 11:56:17Z

I don't think your expectation can be achieved with JSONPath (correct me if I am wrong).

$[?(@.InnerArray[?(@.id==301)])]

is meant to be selecting tokens from the parent array using filters applied to InnerArray property of children objects.

So in English, it means:

Given a parent object, if its InnerArray contains any object which defines an id property and value of such id property is equal to 301, then return the parent object.

To select tokens from InnerArray, you should do (assuming id is unique):

$[?(@.InnerArray[?(@.id==301)])].InnerArray[0]

The result is:

[
   {
      "id":301,
      "innerField1":"f1",
      "innerField2":"f2"
   }
]

Then you need to replace InnerArray in selected parent:

var selectedInnerArray = obj.SelectToken(
    "$[?(@.InnerArray[?(@.id==301)])].InnerArray[0]");
var selectedParent = obj.SelectToken("$[?(@.InnerArray[?(@.id==301)])]");

var result = selectedParent.DeepClone();
result["InnerArray"].Replace(new JArray(selectedInnerArray));

result will look like your expectation.

As for generic filtering with dynamic filter and JSON structure, I don't think I can answer that. You need to merge selected tokens to be able to get your expectation, and I don't know how those merging actions could be easily defined.

Jayesh Tanna · Accepted Answer · 2020-04-21 12:50:22Z

I think its possible to create a model. See below design.

internal class Inner
{
    public int id { get; set; }
    public string innerField1 { get; set; }

    public string innerField2 { get; set; }
}
internal class Outer
{
    public int id { get; set; }
    public string field1 { get; set; }

    public string field2 { get; set; }

    public List<Inner> InnerArray { get; set; }

}

Example of this model as below

private static void Main(string[] args)
    {
        List<Outer> list = new List<Outer>
        {
            new Outer() 
            { 
                id = 1, field1 = "f1", 
                field2 = "f2", 
                InnerArray = new List<Inner>() 
                { 
                    new Inner() 
                    { 
                        id = 1,
                        innerField1="if1",
                        innerField2="if2" 
                    } 
                } 
            },
            new Outer() 
            { 
                id = 2, 
                field1 = "f1", 
                field2 = "f2", 
                InnerArray = new List<Inner>() 
                { 
                    new Inner() 
                    { 
                        id = 1,
                        innerField1="if1",
                        innerField2="if2" 
                    } 
                } 
            }
        };

        string serializedObject = JsonConvert.SerializeObject(list, Formatting.Indented);

        Console.WriteLine(serializedObject);

        Console.ReadLine();

    }

In serializedObject, you will get that json string, again you can deserialize string using JsonConvert.DeserializeObject(...) method and then you can filter out your object using Linq.

 var deserializeList = JsonConvert.DeserializeObject<List<Outer>>(serializedObject);

        var outer = deserializeList.FirstOrDefault(x => x.id == 1);

        Console.WriteLine(outer?.id);

Hope this help.

Hi @Jayesh Tanna I mean I will not be knowing input JSON format so I mentioned I can not create Model for it and yes for the sample JSON this is the Model, Thank You

Collectives™ on Stack Overflow

Filtering Complex JSON by dynamic filter query in c#

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related