How can I create a JSONPath filter expression to search using contains keyword

Question

I have a requirement where I am trying to select objects inside a JSON string by filtering on the presence contains values on properties of objects inside a JSON array. Here is an example of my JSON array:

var Json_String = [{
  'Identifier': 1,
  'CustomerId': 1,
  'AccountType': 'Saving Account',
  'AccountNumber': 'ACC-345678',
  'TransactionDate': '22-09-2020',
  'Narration': 'NXT TXN: RENT FDR SEP2020'
},
{
  'Identifier': 2,
  'CustomerId': 1,
  'AccountType': 'Current Account',
  'AccountNumber': 'ACC-56432',
  'TransactionDate': '23-09-2020',
  'Narration': 'NXT TXN: Depreciation expense'
}]

I want to find a JSON object using Contains keyword like:

I already tried using below query:

var res = Json_String.Where(it => it.Value["AccountType"].ToString().Contains("Saving")).ToList();

Above query giving me the exact result which I want. But In my case, the filter key, Filter criteria, and filter value should be dynamic, which means, I have no idea which value and filter condition should come at the filtering runtime. e.g. I have below format filter criteria in JSON array format which are user-defined.

[{
    "FieldId": "14",
    "Operator": "Contains",
    "ConditionalOperator": null,
    "Sequence": 1,
    "Value": "Saving",
    "OperatorName": "Contains",
    "FieldName": "AccountType"
}, {
    "FieldId": "15",
    "Operator": "GreaterThan",
    "ConditionalOperator": "AND",
    "Sequence": 2,
    "Value": "01/09/2020",
    "OperatorName": "GreaterThan",
    "FieldName": "TransactionDate"
}]

So, I want to create a dynamic JsonPath query with more than one condition using the above format filter criteria JSON array to search for records.

I need the response in c#. As of now. I tried to use the NewtonSoft.Json SelectToken to select a token using a JSONPath query.

var res = Json_String.SelectTokens("$.[?(@.AccountType=='Saving Account')]").ToList();

The above JSONPath query also gives me data by filtering from my actual JSON string array. But when tried to filter data using contains keywords it gives me no result.

var res = Json_String.SelectTokens("$.[?(@.AccountType.Contains('Saving'))]").ToList();

I need the syntax for the "Contains" keyword operator. Any suggestions would be helpful.

To my knowldege, Json.Net does not support a `Contains` operator in a JsonPath expression. However, as of version 11.0.1, it does support regular expressions in JsonPath, so you could use that approach to do your query. See [JSON.NET - find JObject by value regex in complex object?](https://stackoverflow.com/q/60141830/10263) — Brian Rogers, Dec 07 '20 at 23:34
Also related or duplicate: [String Functions in JSONPath](https://stackoverflow.com/q/34191320/3744182). — dbc, Aug 07 '23 at 19:38

score 0 · Answer 1 · answered Dec 07 '20 at 05:02

You can solve this issue using below code logic. First, convert your JSON object to string and then convert it using JSONConvert class. After convert, you can apply all Linq logic on the result set.

string Json_String = "[{
'Identifier': 1,
  'CustomerId': 1,
  'AccountType': 'Saving Account',
  'AccountNumber': 'ACC-345678',
  'TransactionDate': '22-09-2020',
  'Narration': 'NXT TXN: RENT FDR SEP2020'
},
{
  'Identifier': 2,
  'CustomerId': 1,
  'AccountType': 'Current Account',
  'AccountNumber': 'ACC-56432',
  'TransactionDate': '23-09-2020',
  'Narration': 'NXT TXN: Depreciation expense'
}]";

var result = JsonConvert.DeserializeObject(Json_String);
var res = (result as Newtonsoft.Json.Linq.JArray).Where(x => x["AccountType"].ToString().Contains("Saving")).ToList();

How can I create a JSONPath filter expression to search using contains keyword

1 Answers1