在
2019-03年委员会成立后 ，功能冻结了。 我们这里有几乎是传统的专栏文章：我们
已经写了关于去年的冻结。 现在，2019年的结果是：新的更新将包含在PostgreSQL 12中。在本部分的JSONPath专栏中，使用了Oleg Bartunov在今年4月9日在圣彼得堡的
Saint Highload ++上阅读的“ Etudes中的Postgres 12”报告中的示例和片段。
杰森帕特
与JSON（B）相关的所有内容在世界范围内都与俄罗斯相关，这是Postgres Professional最重要的开发领域之一。 用于JSON / JSONB的jsonb类型，函数和运算符出现在PostgreSQL 9.4版中，由Oleg Bartunov领导的团队制作。
SQL / 2016标准提供了使用JSON的功能：此处提到了JSONPath-JSON中的一组数据寻址工具； JSONTABLE-将JSON转换为常规表的方法； 大量的功能和运算符。 尽管Postgres中的JSON已获得长期支持，但2017年Oleg Bartunov及其同事开始致力于支持该标准。 符合标准总是好的。 在标准中描述的所有内容中，只有一个但最重要的补丁是版本12中的JSONPath，因此我们将首先讨论它。
在远古时代，人们使用JSON并将其存储在文本字段中。 在9.3中，出现了一种特殊的JSON数据类型，但与之相关的功能并不丰富，由于解析JSON的文本表示所花费的时间，这种类型的请求工作缓慢。 这阻止了许多使用NoSQL数据库的潜在Postgres用户。 由于O. Bartunov，A。Korotkov和F. Sigaev，Postgres的生产率提高了9.4，这是因为Postgres引入了JSON的二进制版本-jsonb类型。
不需要每次都解析jsonb，因此使用它要快得多。 在与此同时出现的新函数和运算符中，有些仅适用于新的二进制类型，例如出现的重要运算符
@> ，用于检查给定JSONB中是否包含元素或数组：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
给出TRUE，因为右侧的数组进入左侧的数组。 但是
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
将给出FALSE，因为嵌套级别不同，因此必须显式设置它。 是否为jsonb类型引入了存在运算符
？ （一个问号），它检查字符串是JSONB值顶层的对象键还是数组的元素，以及两个类似的运算符（
在此处详细说明）。 具有两类GIN运算符的GIN索引支持它们。 运算符
-> （箭头）允许您通过JSONB“导航”，它按键返回值，如果是数组，则按索引返回值。 还有
更多的移动运营商。 但是没有办法组织像WHERE一样起作用的过滤器。 这是一个突破：借助jsonb，Postgres作为具有NoSQL功能的RDBMS开始流行。
2014年，A。Korotkov，O。Bartunov和F. Sigaev开发了jsquery扩展，该扩展被包含在Postgres Pro Standard 9.5（以及后来的Standard和Enterprise版本）中。 它提供了用于处理json（b）的其他非常广泛的功能。 此扩展定义了用于从json（b）中提取数据的查询语言和索引以加快这些查询的速度。 用户需要此功能，他们还没有准备好等待标准以及在香草版本中包含新功能。 该开发项目由Wargaming.net赞助，也证明了其实用价值。 该扩展实现了一种特殊的类型-jsquery。
使用这种语言的查询是紧凑的，并且看起来像这样：
SELECT '{"apt":[{"no": 1, "rooms":2}, {"no": 2, "rooms":3}, {"no": 3, "rooms":2}]}'::jsonb @@ 'apt.#.rooms=3'::jsquery;
我们在这里问公寓楼中是否有“三卢布”。 必须指定jsquery类型，因为@@运算符现在也为jsonb类型。
这里是说明，
这里是带有许多示例的演示。
总计：Postgres已经具备使用JSON的所有功能，然后出现了SQL：2016标准。 事实证明，它的语义与jsquery扩展中的语义没有太大不同。 该标准的作者甚至可能浏览了jsquery并发明了JSONPath。 我们的团队必须实施一些与现有产品有所不同的东西，当然还有很多新事物。
一年多以前，在3月的commitfest上，我们以3个支持
SQL：2016标准的大补丁的形式向社区提供了我们编程工作的成果：
SQL / JSON：JSONPath;
SQL / JSON：函数；
SQL / JSON：JSON_TABLE。
但是要开发补丁程序并不是一件容易的事，推广它们也不是一件容易的事，尤其是当补丁程序很大且影响许多模块时。 需要进行许多次修订，修订版必须像商业公司那样推广，要投入大量资源（工时）。 Postgres Professional的首席架构师Alexander Korotkov亲自负责（因为他现在拥有提交者的身份）并实现了JSONPath补丁的采用-JSONPath补丁是本系列补丁中的主要补丁。 第二个和第三个现在处于“需求审查”状态。 聚焦的JSONPath允许您使用JSON（B）结构，并且足够灵活以突出显示其片段。 该标准规定的15个要点中，有14个已实现，这比Oracle，MySQL和MS SQL中的要多。
JSONPath表示法与Postgres语句在使用JSON和JSQuery表示法方面有所不同。 层次结构由点表示：
$ .abc（用postgres 11表示法，我必须写'a'->'b'->'c'）；
$-元素的当前上下文-实际上，带有$的表达式定义了要处理的json（b）区域，包括过滤器中的一个，其余部分则无法使用；
@-过滤器表达式中的当前上下文-使用$遍历表达式中可用的路径；
[*]-数组；
*-通配符，在带有$或@的表达式中表示路径段的任何值，但要考虑到层次结构；
**-作为带有$或@的表达式的一部分，可以表示路径段的任何值，而无需考虑层次结构-如果您不知道元素的嵌套级别，则使用它很方便；
运算符“？” 允许您组织类似于WHERE的过滤器：
$ .abc？ （@ .x> 10）；
$ .abcxtype（）以及size（），double（），ceiling（），floor（），abs（），datetime（），keyvalue（）是方法。
使用jsonb_path_query函数（关于以下函数）的查询可能看起来像这样：
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > 3)'); jsonb_path_query_array
尽管没有提交带有功能的特殊补丁，但JSONPath补丁已经具有用于JSON（B）的关键功能：
jsonb_path_exists('{"a": 1}', '$.a') true ( "?") jsonb_path_exists('{"a": 1}', '$.b') false jsonb_path_match('{"a": 1}', '$.a == 1') true ( "@>") jsonb_path_match('{"a": 1}', '$.a >= 2') false jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3, 4, 5 jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') 0 jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') [3, 4, 5] jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') [] jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3 jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') NULL
请注意，JSONPath表达式中的相等性是单个“ =”，而在jsquery中则是双重的：“ ==”。
为了获得更优雅的插图，我们将在单列房屋牌中生成JSONB：
CREATE TABLE house(js jsonb); INSERT INTO house VALUES ('{ "address": { "city":"Moscow", "street": "Ulyanova, 7A" }, "lift": false, "floor": [ { "level": 1, "apt": [ {"no": 1, "area": 40, "rooms": 1}, {"no": 2, "area": 80, "rooms": 3}, {"no": 3, "area": 50, "rooms": 2} ] }, { "level": 2, "apt": [ {"no": 4, "area": 100, "rooms": 3}, {"no": 5, "area": 60, "rooms": 2} ] } ] }');
图1：带有分配的叶子单元的“住房JSON”树。这是一个奇怪的JSON：它具有混乱的层次结构，但它是取自生活的，在生活中，经常有必要使用实际存在的东西，而不是应该使用实际的东西。 有了新版本的功能，我们将在1楼和2楼找到公寓，但不在楼层公寓列表中找到第一个（在树上以绿色突出显示）：
SELECT jsonb_path_query_array(js, '$.floor[0, 1].apt[1 to last]') FROM house;
在PostgreSQL 11中，您必须提出以下要求：
SELECT jsonb_agg(apt) FROM ( SELECT apt->generate_series(1, jsonb_array_length(apt) - 1) FROM ( SELECT js->'floor'->unnest(array[0, 1])->'apt' FROM house ) apts(apt) ) apts(apt);
现在是一个非常简单的问题：是否有行（任何地方）包含“莫斯科”值？ 真的很简单：
SELECT jsonb_path_exists(js, '$.** ? (@ == "Moscow")') FROM house;
在版本11中，您将必须编写一个巨大的脚本：
WITH RECURSIVE t(value) AS ( SELECT * FROM house UNION ALL ( SELECT COALESCE(kv.value, e.value) AS value FROM t LEFT JOIN LATERAL jsonb_each ( CASE WHEN jsonb_typeof(t.value) = 'object' THEN t.value ELSE NULL END ) kv ON true LEFT JOIN LATERAL jsonb_array_elements ( CASE WHEN jsonb_typeof(t.value) = 'array' THEN t.value ELSE NULL END ) e ON true WHERE kv.value IS NOT NULL OR e.value IS NOT NULL ) ) SELECT EXISTS (SELECT 1 FROM t WHERE value = '"Moscow"');
图2找到了JSON树，莫斯科！我们正在寻找面积为40至90平方米的任何楼层的任何公寓：
select jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area > 40 && @.area < 90)') FROM house; jsonb_path_query ----------------------------------- {"no": 2, "area": 80, "rooms": 3} {"no": 3, "area": 50, "rooms": 2} {"no": 5, "area": 60, "rooms": 2} (3 rows)
我们正在使用我们的住房杰森（Jason）寻找3号以后有房间的公寓：
SELECT jsonb_path_query(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query
这是jsonb_path_query_first的工作方式：
SELECT jsonb_path_query_first(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query_first
我们看到仅选择满足过滤条件的第一个值。
JSONB @@的布尔JSONPath运算符称为匹配运算符。 它通过调用jsonb_path_match_opr函数来计算JSONPath谓词。
另一个布尔运算符是@？ -这是一个存在性测试，回答JSONPath表达式是否将返回SQL / JSON对象的问题，它调用jsonb_path_exists_opr函数：
'[1,2,3]' @@ '$[*] == 3' true; '[1,2,3]' @? '$[*] @? (@ == 3)' - true
使用不同的运算符可以达到相同的结果：
js @? '$.a' js @@ 'exists($.a)' js @@ '$.a == 1' js @? '$ ? ($.a == 1)'
JSONPath布尔运算符的美丽之处在于它们得到GIN索引的支持和加速。 jsonb_ops和jsonb_path_ops是相应的运算符类。 在此示例中，由于有一个微型表，因此我们禁用了SEQSCAN，因此在大型表上，优化器本身将选择位图索引：
SET ENABLE_SEQSCAN TO OFF; CREATE INDEX ON house USING gin (js); EXPLAIN (COSTS OFF) SELECT * FROM house WHERE js @? '$.floor[*].apt[*] ? (@.rooms == 3)'; QUERY PLAN
jsonb_path_xxx（）形式的所有函数都具有相同的签名：
jsonb_path_xxx( js jsonb, jsp jsonpath, vars jsonb DEFAULT '{}', silent boolean DEFAULT false )
vars是用于传递JSONPath变量的JSONB对象：
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > $x)', vars => '{"x": 2}'); jsonb_path_query_array
当我们在其中一张表中进行涉及jsonb类型字段的联接时，没有var很难做到。 假设我们制作了一个应用程序，该应用程序在那座房子里为员工准备了合适的公寓，这些员工在问卷中写下了他们对最小面积的要求：
CREATE TABLE demands(name text, position text, demand int); INSERT INTO demands VALUES ('','', 85), ('',' ', 45); SELECT jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area >= $min)', vars => jsonb_build_object('min', demands.demand)) FROM house, demands WHERE name = ''; -[ RECORD 1 ]
Lucky Pasha可以提供4套公寓。 但是值得在请求中更改1个字母-从“ P”更改为“ C”，并且别无选择！ 只有一间公寓可以。
还有一个关键字：silent是禁止执行错误处理的标志；它们是程序员的良心。
SELECT jsonb_path_query('[]', 'strict $.a'); ERROR: SQL/JSON member not found DETAIL: jsonpath member accessor can only be applied to an object
错误。 但这不会是一个错误：
SELECT jsonb_path_query('[]', 'strict $.a', silent => true); jsonb_path_query
顺便说一下，关于错误：按照标准，表达式中的算术错误不会给出错误消息，它们是程序员的良心：
SELECT jsonb_path_query('[1,0,2]', '$[*] ? (1/ @ >= 1)'); jsonb_path_query
在过滤器中计算表达式时，将搜索数组值，其中包含0，但除以0不会产生错误。
功能根据所选模式的不同而有所不同：“严格”或“宽松”（在“非严格”或“宽松”的翻译中，默认情况下已选中）。 假设我们正在JSON中寻找Lax模式下的密钥，但显然不是这样：
SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)'; ?column?
现在处于严格模式下：
SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)'; ?column?
也就是说，在自由模式下，我们收到FALSE，在严格的情况下我们得到NULL。
在Lax模式下，具有复杂层次结构[1,2，[3,4,5]]的数组始终扩展为[1,2,3,4,5]：
SELECT jsonb '[1,2,[3,4,5]]' @? 'lax $[*] ? (@ == 5)'; ?column?
在严格模式下，将找不到数字“ 5”，因为它不在层次结构的底部。 要找到它，您必须修改查询，用“ @ [*]”替换“ @”：
SELECT jsonb '[1,2,[3,4,5]]' @? 'strict $[*] ? (@[*] == 5)'; ?column?
在PostgreSQL 12中，JSONPath是一种数据类型。 该标准没有说明是否需要新的类型，它是实现的属性。 使用新类型，我们可以在操作员和索引的帮助下使用jsonpath进行全面的工作，以加速其工作，而JSONB已经存在。 否则，必须在执行程序和优化程序代码级别集成JSONPath。
例如，您可以
在此处阅读有关SQL / JSON语法的
信息 。
Oleg Bartunov的博客文章介绍了PostgreSQL，Oracle，SQL Server和MySQL的
SQL / JSON standard-2016一致性 。
这是有关SQL / JSON的
演示 。
这是SQL / JSON的
介绍 。