如何创建同时包含AND和OR运算符的JSONPath过滤器表达式? [英] How can I create a JSONPath filter expression containing both AND and OR operators?

问题描述

我有一个要求，我要通过过滤JSON数组中对象的多个属性的存在和/或值来尝试选择JSON字符串中的对象.

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.

这是我的JSON的示例:

Here is an example of my JSON:

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

我想使用这样的查询来检查这个json字符串(下面的查询是一个SQL查询)

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

我尝试使用JSONPath以及system.linq.dynamic，但没有运气.

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

更新

我需要c#中的响应.截至目前.我尝试使用NewtonSoft.Json SelectToken通过JSONPath查询选择令牌.

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')]");             

查询的第二部分是:

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

问题出在"and"运算符上-我不知道如何在单个JSONPath查询中组合它们.个别地查询正在工作.我需要和"运算符的语法.任何建议都会有所帮助.

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.

推荐答案

Newtonsoft的JSONPath实现确实支持AND运算符，如

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)]");

但是，您正在混合 &&和||运算符，不幸的是，这两个运算符的优先级顺序没有记录. JSONPath网站毫无用地说，()是脚本表达式，使用了底层的. Newtonsoft文档完全没有说明除了将读者引荐到相同的JSONPath网站之外！

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!

您可能想打开有关Newtonsoft的文档问题，要求他们澄清优先顺序中的||和&&运算符.

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. 我从实验中发现，&&和||从左到右具有相同的优先级.因此，A && B || C表示A && (B || C)，而A || B && C表示A || (B && C)

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();

您可以使用 Enumerable.Intersect() 来组合查询结果:

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();

您可以使用 Enumerable.Where() 过滤，然后使用SelectTokens()进行枚举:

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();

如果您只想知道是否有任何匹配项，而不是匹配的令牌列表，则可以使用 Any() ，例如

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:

1. 尝试括住逻辑运算符会引发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();

在||错误选择的与||子句之一匹配但与&&子句不匹配的令牌之后放置&&:

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.

这篇关于如何创建同时包含AND和OR运算符的JSONPath过滤器表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！