How can I create a JSONPath filter expression containing both AND and OR operators?

Question

I have a requirement where i am trying to select objects inside a JSON string by filtering on the presence and/or values of multiple properties of objects inside a JSON array.

Here is an example of my JSON:

{'Fields':[{
  "Sub_Status": "Pending",
  "Status": "Pending",
  "Patient_Gender": "M"
}]}

I want to check this json string using query like this (below query is a SQL query)

string query = TRIM(UPPER(Status)) IN ('PENDING', 'DISPENSED','SHIPMENT') AND TRIM(Patient_Gender) IS NOT NULL

string json_string = {'Fields':[{
  "Sub_Status": "Pending",
  "Status": "Pending",
  "Patient_Gender": "M"
}]};

var test = json_string.Where(query).toString() /// a return of bool = true or false

I tried using JSONPath and also system.linq.dynamic, But no luck.

UPDATE

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.

The first part of my query as of now is:

JToken test = s.SelectToken("$.Fields[?(@.Status == 'Pending' || @.Status == 'PENDING' || @.Status == 'SHIPMENT' || @.Status == 'DISPENSED')]");             

The second part of my query is:

JToken test = s.SelectToken("$.Fields[?(@.Patient_Gender != '')]");

The problem is with "and" operator -- I do not know how to combine them in a single JSONPath query. Individually the queries are working. I need the syntax for the "and" operator. Any suggestions would be helpful.

Answer 1

Newtonsoft's JSONPath implementation does support an AND operator, as can be seen in the source for QueryExpression.cs . It uses the && syntax. Thus if you want to search for fields that have Status == 'Pending' and have a Patient_Gender property present (with any value), you would do:

var query = s.SelectTokens("$.Fields[?(@.Status == 'Pending' && @.Patient_Gender)]");

However, you are mixing && and || operators, and unfortunately the order of precedence of these two operators is undocumented. The JSONPath website says, uselessly, that () is a script expression, using the underlying script engine. And the Newtonsoft documentation says nothing at all beyond referring the reader to that same JSONPath website!

You might want to open a documentation issue with Newtonsoft asking them to clarify the precedence of the || and && operators in JSONPath.

Thus to get your desired query, you have the following options:

1. I have found from experiment that && and || associate left-to-right with equal precedence. Thus A && B || C means A && (B || C) while A || B && C means A || (B && C)

   You apparently want the former. As long as you are willing to depend on undocumented behavior, the following query should work:

   var filter = "$.Fields[?(@.Patient_Gender && @.Status == 'Pending' || @.Status == 'PENDING' || @.Status == 'SHIPMENT' || @.Status == 'DISPENSED')]";
   var query = s.SelectTokens(filter);
   var result = query.ToList();
   

2. You can use Enumerable.Intersect() to combine query results:

   var query1 = s.SelectTokens("$.Fields[?(@.Status == 'Pending' || @.Status == 'PENDING' || @.Status == 'SHIPMENT' || @.Status == 'DISPENSED')]");
   var query2 = s.SelectTokens("$.Fields[?(@.Patient_Gender)]");
   var query = query1.Intersect(query2);
   
   var result = query.ToList();
   

3. You can use a Enumerable.Where() to do the filtering and just use SelectTokens() to do the enumeration:

   var query = from t in s.SelectTokens("$.Fields[*]")
               where (string)t["Patient_Gender"] != null
               let status = (string)t["Status"]
               where status == "Pending" || status == "PENDING" || status == "SHIPMENT" || status == "DISPENSED"
               select t;
   
   var result = query.ToList();
   

If you just want to know if anything matches, rather than a list of tokens that match, you can use Any(), e.g.

var result = query.Any();

Incidentally, the following attempts did not work:

4. Trying to parenthesize the logical operators throws a JsonException: Unexpected character while parsing path query: (:

   var filter = "$.Fields[?((@.Status == 'Pending' || @.Status == 'PENDING' || @.Status == 'SHIPMENT' || @.Status == 'DISPENSED') && (@.Patient_Gender))]";
   var query = s.SelectTokens(filter);
   var result = query.ToList();
   

5. Puttting && after || incorrectly selected tokens that matched one of the || clauses but not the && clause:

   var filter = "$.Fields[?(@.Status == 'Pending' || @.Status == 'PENDING' || @.Status == 'SHIPMENT' || @.Status == 'DISPENSED' && @.Patient_Gender)]";
   var query = s.SelectTokens(filter);
   var result = query.ToList();
   

Here is a fiddle showing the both the working and non-working queries.