db2数据库查询 db2数据库查询json

db2 给用户赋权表赋权

Why you should integrate a JSON query processor into your API, SDK or CLI

为什么应将JSON查询处理器集成到API，SDK或CLI

JSON is probably the most common format nowadays for open data interchange. Although it’s designed as a lightweight JavaScript-object-like format, JSON documents can get quite large especially if they contain deeply nested objects and arrays.

JSON可能是当今开放数据交换最常用的格式。 尽管JSON文档被设计为类似于JavaScript对象的轻量级格式，但是JSON文档可能会变得很大，特别是如果它们包含深度嵌套的对象和数组。

There’s a real need to be able to run general processing queries on JSON documents — for filtering, shaping and transforming JSON data.

真正需要能够在JSON文档上运行常规处理查询-以便过滤，调整和转换JSON数据。

Some document databases like MongoDB and PostgreSQL have their own query language that allows running complex queries on JSON, but that’s usually irrelevant when our JSON data is outside the of context of database records (although you can use MingoJS, a JavaScript implementation of MongoDB query language). What if we could provide users with built-in support for such queries?

诸如MongoDB和PostgreSQL之类的一些文档数据库都有其自己的查询语言，该查询语言允许在JSON上运行复杂的查询，但是当我们的JSON数据不在数据库记录的上下文之外时，这通常是无关紧要的(尽管您可以使用MingoJS ，这是MongoDB查询语言JavaScript实现) )。 如果我们可以为用户提供对此类查询的内置支持怎么办？

(The use case:)

1. Your software outputs some JSON and you want to give the user an easy, standardized way to filter or manipulate the data (examples: APIs, SDKs, CLIs, online playgrounds)
   您的软件输出一些JSON，并且您想为用户提供一种简单，标准化的方式来过滤或处理数据(例如： API，SDK，CLI，在线游乐场 )
2. Your software takes JSON as input and you want to programmatically filter or manipulate the data in a uniform and reusable way. (Think JSON documents with arrays of objects nesting more arrays and objects). Yes, you can write your own logic using map, filter and reduce functions, but maybe there’s a better, more declarative way.

This post focuses mainly only on the first use case, where you want to give your users a built-in option to process the output (but in fact, the two cases are similar).

这篇文章主要只关注第一个用例，您要为用户提供一个内置选项来处理输出 (但实际上，这两种情况是相似的)。

(Candidates for JSON query languages)

XML has XPath for querying and traversing XML nodes. What is the equivalent for JSON?

XML具有 用于查询和遍历XML节点的 XPath 。 JSON相当于什么？

(JSONPath)

JSONPath — “XPath for JSON” — is the first query language that comes to mind. It’s one of the earliest implementations of a JSON query language, and it gets the job done. One nice thing about it is that it allows traversing the entire JSON tree (accessing parent nodes, for example) and can output path locations in the tree as JSON-pointers (nested keys).

JSONPath (“ JSON的XPath”)是我想到的第一种查询语言 。 它是JSON查询语言的最早实现之一，它可以完成工作。 关于它的一件好事是，它允许遍历整个JSON树(例如，访问父节点)，并且可以将树中的路径位置作为JSON指针(嵌套键)输出。

The problem is that JSONPath’s syntax is not so intuitive, if we wish to provide an interface based on it to our users. And a bigger issue is that it does not have a tight specification, which means that there are numerous implementations of JSONPath out there that might give different results. This makes it hard to rely on JSONPath for many scenarios.

问题在于，如果我们希望向用户提供基于JSONPath的接口，那么它的语法就不是那么直观。 还有一个更大的问题是它没有严格的规范，这意味着那里有许多JSONPath实现可能会产生不同的结果。 这使得在许多情况下都很难依赖JSONPath。

(jq)

jq is command-line JSON processor that uses a powerful JSON query language. You can do almost anything with jq! A general purpose language that is popular among DevOps people, probably due to the CLI that is easy to pipe another process’s output through, and then use jq’s pipe system to process that formatted output through additional pipes.

jq是使用强大的JSON查询语言的命令行JSON处理器。 您可以使用jq进行几乎所有操作！ 在DevOps人群中流行的通用语言，可能是由于CLI易于通过管道传输另一个进程的输出，然后使用jq的管道系统通过其他管道处理格式化的输出。

Two problems, though: jq is a command-line tool, not an interface we can provide to users in our own APIs, SDKs and so on. Libraries like node-jq and java-jq are simply wrappers around the jq binary, and may suffer from performance issues, which could be crucial for real time performance. Secondly, with great power comes great complexity — and some users claim that jq is too complex of a language and may be overkill for our use case, since it allows much more than just manipulating existing JSON.

但是，有两个问题： jq是命令行工具，而不是我们可以通过自己的API，SDK等提供给用户的接口。 像node-jq和java-jq这样的库只是围绕jq二进制文件的包装，并且可能会遇到性能问题，这对于实时性能至关重要。 其次，强大的功能带来了极大的复杂性-一些用户声称jq语言太复杂了，对于我们的用例而言可能过于矫kill过正，因为jq所提供的不仅仅是操作现有的JSON。

(JMESPath)

JMESPath is a query language for JSON that allows complex filtering, extraction and transformation on JSON documents. Unlike JSONPath and jq, it has a complete specification with precise grammar, so the syntax is well defined in all implementations. It has fully compliant libraries in a host of languages (Java, Python, JavaScript, Go, PHP and more). It is a powerful query language and yet the syntax is quite simple.

JMESPath是JSON的查询语言，它允许对JSON文档进行复杂的过滤，提取和转换。 与JSONPath和jq不同，它具有完整的规范和精确的语法，因此在所有实现中都很好地定义了语法。 它具有多种语言(Java，Python，JavaScript，Go，PHP等)的完全兼容的库。 它是一种功能强大的查询语言，但语法非常简单。

For these reasons, JMESPath is our language of choice when it comes to integrating it in our own services, as a user-facing interface. It has a friendly playground and tutorial that we can provide users as reference.

由于这些原因，在将JMESPath集成到我们自己的服务中作为面向用户的界面时，它是我们选择的语言。 它有一个友好的游乐场和教程 ，我们可以为用户提供参考。

The examples on https://jmespath.org/examples.html can give you an idea of the power and simplicity of the language.

https://jmespath.org/examples.html上的示例可以使您了解该语言的强大功能和简单性。

Popularity comparison of JMESPath, JSONPath and JQ — in the JavaScript / npm worlds JMESPath，JSONPath和JQ的流行度比较-在JavaScript / npm世界中

给用户的力量 (Power to the User)

Thanks to libraries, adding JMESPath to our service’s code is very simple. By integrating JMESPath we can now provide our API, SDK or CLI users with a powerful, simple and standardized method to shape the JSON response returned from our service, without needing external tooling.

多亏了库，将JMESPath添加到我们的服务代码中非常简单。 通过集成JMESPath，我们现在可以为API，SDK或CLI用户提供一种强大，简单且标准化的方法，以调整从我们的服务返回的JSON响应，而无需外部工具。

As an example, we’ll assume our service is a public REST API. By giving the user the ability to specify a JMESPath query on the data, they end up saving time, bytes on the wire and lines of code doing post-processing, since the processing happens on the server and the response contains exactly what the user needs.

作为示例，我们假设我们的服务是公共REST API。 通过使用户能够对数据指定JMESPath查询，他们最终节省了时间，行中的字节和进行后处理的代码行，因为处理发生在服务器上，并且响应包含用户所需的内容。

Integrating JMESPath in our API handler code is quite simple, as the library has only one function (“search”). Here as an example in Node.js / JavaScript:

将JMESPath集成到我们的API处理程序代码中非常简单，因为该库只有一个功能(“搜索”)。 这里以Node.js / JavaScript为例：

const jmespath = require(‘jmespath’);const requestHandler = (req, res, next) => {
    // response res was passed here from previous handler 
    if (req.query && req.query.filter) {
        res = applyJmesPathFilter(res, req.query.filter); 
    } // ...more logic and eventually res.send()
}const applyJmesPathFilter = (res, jmespathExpression) => {
    try {
       res = jmespath.search(res, jmespathExpression) || {}; 
    } 
    catch (e) { 
        res = {}; 
        log.error(e);
    }
    return res;
}

This way, users can immediately use the filter query parameter to specify a JMESPath expression to process the initial JSON result:

这样，用户可以立即使用filter查询参数来指定JMESPath表达式来处理初始JSON结果：

GET /api/players?filter=[JMESPATH_EXPRESSION]

GET / api / players？filter = [JMESPATH_EXPRESSION]

Let’s assume our service’s output is the NBA players API, where information on some legendary basketball players live. A GET /api/players request returns the following JSON array of objects:

假设我们服务的输出是NBA球员API，其中包含一些传奇篮球运动员的信息。 GET /api/players请求返回以下对象的JSON数组：

[
 {
  "playerId": "7c3f73dd-0b38-48dc-9347-c78811bd80c4",
  "playerName": "Scottie Pippen",
  "yearOfBirth": "1965",
  "collegeId": "77302082-2758-48cc-ab3a-7b811a8bdf80",
  "jerseyNumber": "33",
  "playerStats": {
      "points": 18940,
      "rebounds": 7494,
      "assists": 6135
  },
  "teamNames": [
     "Chicago Bulls",
     "Portland Trail Blazers",
     "Houston Rockets",
     "Chicago Bulls"
  ]
 },
 {
  "playerId": "8d75bb0f-a444-4264-a583-4ca5799169cf",
  "playerName": "Patrick Ewing",
  "yearOfBirth": "1962",
  "collegeId": "0456a17b-320d-4ddc-bfc2-011670af2b77",
  "jerseyNumber": "33",
  "playerStats": {
     "points": 24815,
     "rebounds": 11617,
     "blocks": 2894
  },
  "teamNames": [
     "New York Knicks",
     "Seattle SuperSonics",
     "Orlando Magic" 
  ]
 } 
]

Since the original result is an array, the JMESPath expressions below start with “[]” to reference that array.

由于原始结果是一个数组，因此下面的JMESPath表达式以“ []”开头以引用该数组。

GET /api/players?filter=[].playerName will return the following array:

GET /api/players?filter=[].playerName将返回以下数组：

[
  "Scottie Pippen",
  "Patrick Ewing"
]

We can also ask for objects instead, and modify the original key names:GET /api/players?filter=[].{name: playerName, number: jerseyNumber} returns:

我们也可以要求对象，并修改原始键名： GET /api/players?filter=[].{name: playerName, number: jerseyNumber}名称GET /api/players?filter=[].{name: playerName, number: jerseyNumber}返回：

[
  {
    "name": "Scottie Pippen",
    "number": "33"
  },
  {
    "name": "Patrick Ewing",
    "number": "33"
  }
]

We can run filter queries based on one or more keys, and pipe the result to format it (here as plain string):

我们可以基于一个或多个键运行过滤器查询，并通过管道将结果格式化(此处为纯字符串)：

GET /api/players?filter=[?yearOfBirth > `1964`].playerName | [0] returns

GET /api/players?filter=[?yearOfBirth > `1964`].playerName | [0] GET /api/players?filter=[?yearOfBirth > `1964`].playerName | [0]返回

"Scottie Pippen"

And a more advanced logic, which uses the sort() and join() functions:

还有一个更高级的逻辑，它使用sort()和join()函数：

GET /api/players?filter=[].playerName | sort(@) | { nbaLegends: join(‘, ‘, @) }

GET /api/players?filter=[].playerName | sort(@) | { nbaLegends: join(', ', @) }

{
  "nbaLegends": "Patrick Ewing, Scottie Pippen"
}

The user can shape the result so it is tailored to their exact needs. For example:

用户可以调整结果的形状，以便根据自己的实际需求进行调整。 例如：

GET /api/players?filter=[].{name: playerName, firstTeam: teamNames | [0], points: playerStats.points }

GET /api/players?filter=[].{name: playerName, firstTeam: teamNames | [0], points: playerStats.points }

[
  {
    "name": "Scottie Pippen",
    "firstTeam": "Chicago Bulls",
    "points": 18940
  },
  {
    "name": "Patrick Ewing",
    "firstTeam": "New York Knicks",
    "points": 24815
  }
]

And these are just a few of the JSON processing options available through JMESPath.

这些只是JMESPath提供的一些JSON处理选项。

Some readers may notice that enabling the user to “get exactly the data they want” can be achieved by using GraphQL. True, but this here is a REST API :) (and the scenario could be replaced by an SDK or CLI). In fact, GraphQL doesn’t give you the processing power that JSON query languages like JMESPath provide. Of course, the JSON processing runs after the initial dataset has been fetched entirely, which is not as efficient as filtering early with resolvers on the Data Access Layer, achieved in GraphQL APIs.

一些读者可能会注意到，使用GraphQL可以使用户“准确获取他们想要的数据”。 是的，但这是REST API：)(并且该方案可以用SDK或CLI代替)。 实际上，GraphQL不能给您像JMESPath这样的JSON查询语言提供的处理能力。 当然，JSON处理是在完全提取初始数据集之后运行的，其效率不如在GraphQL API中实现的在数据访问层上使用解析器进行早期过滤有效。

I’ve integrated JMESPath into some of Sisense public REST APIs and as a feature in the GraphQL2REST package.

我已经将JMESPath集成到Sisense的一些公共REST API中，并作为GraphQL2REST包中的功能。

(Limitations)

JMESPath has some limitations compared to jq and JSONPath. The biggest limitation for me is that there is no easy way to get the list of paths of a given JSON document (aka node names or JSON pointers), and you can’t express keys as JMESPath expressions (so as to run operations on a set of keys which satisfy a test). Recursive traversal is not possible — you have to specify the full path to the JSON key. Another issue is that JMESPath does not allow referencing parent nodes when iterating. These are limitations that restrict usage for developers sometimes, but usually do not affect the common use cases discussed here.

与jq和JSONPath相比，JMESPath有一些限制。 对我来说，最大的限制是没有简单的方法来获取给定JSON文档的路径列表(即节点名或JSON指针)，并且您不能将键表示为JMESPath表达式(以便在一组满足测试要求的键)。 递归遍历是不可能的-您必须指定JSON密钥的完整路径。 另一个问题是，JMESPath不允许在迭代时引用父节点。 这些限制有时会限制开发人员的使用，但通常不会影响此处讨论的常见用例。

(Additional Resources)

(For adventurers — more query languages:)

• YAQL (Yet Another Query Language) YAQL(但另一种查询语言)
• rq query language rq查询语言
• jl (JSON lambda) query language jl(JSON lambda)查询语言
• glom (Python) glom(Python)
• object-path (JavaScript) 对象路径(JavaScript)

jp: a command line interface (CLI) for JMESPath

jp：JMESPath的命令行界面(CLI)

翻译自: https://levelup.gitconnected.com/json-queries-give-your-users-jmespath-power-ef8ab0d38553

db2 给用户赋权表赋权