PostgreSQL的Json路径功能

Question

在文档中，一些PostgreSQL JSON函数使用了JSON路径属性。例如，jsonb_set函数：

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

我找不到这种属性的任何规格说明。

例如，它能否用于根据其属性值检索数组元素？

- mpiffault

---

嗨！我不太明白你的意思。你能更具体地说明一下这种属性的规格吗？这样我们就可以避免误解你的问题了。 - Jim Jones

好的，更具体地说，“path”变量的语法是什么？ - mpiffault

我认为这是一个代表您节点路径的json表示。 - Jim Jones

Answer 1

路径类似于文件系统上的路径：每个值按照指定的顺序进一步深入树的叶子。从通过路径提取特定的JSONB值后，如果需要，可以链接其他JSONB操作。在具有嵌套JSONB对象时，使用带有JSONB路径的函数/运算符通常很有用，但也可以处理简单的JSONB数组。

例如：

SELECT '{"a": 42, "b": {"c": [1, 2, 3]}}'::JSONB #> '{b, c}' -> 1;

...应返回2。

路径{b，c}首先获取b的值，即{"c"：[1, 2, 3]}。 接下来，它深入到获取c的值，即[1, 2, 3]。 然后将->操作链接到该值上，该操作获取该数组中指定索引处的值（使用基于零的表示法，因此0是第一个元素，1是第二个元素等）。如果您使用->，则它将返回具有JSONB数据类型的值，而->>将返回具有TEXT数据类型的值。

但您也可以像这样编写：

SELECT '{"a": 42, "b": {"c": [1, 2, 3]}}'::JSONB #> '{b, c, 1}';

...并将键和数组索引简单地包含在同一路径中。

对于数组，以下两个应该是等效的，除了第一个使用路径，第二个期望一个数组并获取指定索引处的值：

SELECT '[1, 2, 3]'::JSONB #> '{1}';

SELECT '[1, 2, 3]'::JSONB -> 1;

请注意，路径必须始终使用JSON数组语法表示，其中每个连续的值都是您想要深入钻取的树中的下一个叶子。如果它是JSONB对象，则提供键，如果它是JSONB数组，则提供索引。如果将其视为文件路径，则JSONB键类似于文件夹，而数组索引则类似于文件。

- RWalker

---

这个“JSON数组语法”有文档说明吗？我找不到它。 - mpiffault

根据您提供的定义，无法通过其属性值搜索数组中的元素。我理解得对吗？ - mpiffault

JSON并非由PostgreSQL定义，它是一个Web标准：https://www.json.org/https://www.w3schools.com/js/js_json_arrays.asp - RWalker

就使用“其属性值之一的值”来搜索JSON数组元素而言，目前PostgreSQL尚不支持完整的jsonpath语法。您目前必须使用类似CTE的解决方法。有关此问题的一些详细信息在此处：http://gmile.me/jsonpath/ - RWalker

你提到了“JSON数组语法”，但它是用大括号（'{1}'）编写的。这就是为什么我认为你谈论的不是实际的JSON数组而是其他东西的原因。 - mpiffault

是的，我希望JSON路径语法更容易作为文档提供，这样使用起来就更清晰。我只在简单情况下偶尔使用过它。我发现JSON路径语法的官方来源仅包含在SQL:2016标准中（即非免费）。这篇文章解释了更多细节：https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016 - RWalker