在示例表中：
CREATE TABLE example (
id SERIAL PRIMARY KEY,
data JSON NOT NULL );
INSERT INTO example (id, data) VALUES
(1, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]')
, (2, '[{"key": "1", "value": "val1"}]')
, (3, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]');
我想查询数据列中的值字段，其中 key = 2。 我目前使用的查询是这样的：
SELECT id,
jsonb_path_query(
TO_JSONB(data),
'$[*] ? (@.key == "2").value'::JSONPATH
)::VARCHAR AS values
FROM example
我希望结果是：
| id | |
|---|---|
| 1 | "> | val2">
| 2 | >空 |
| 3 | "> | val2">
标量函数处理一个值，它可以使它无效，但集合返回函数生成值，因此它最终可能根本不生成值。如前所述，您可以使用标量函数
SELECT id, jsonb_path_query_first(data::jsonb, '$[*]?(@.key=="2").value')
FROM example;
或者，您可以通过将 SRF(集合返回函数jsonb_path_query())包装在标量子查询中或替换其隐式内部连接来保持→ setof jsonb。两者都将指示缺少带有 null 的生成值，但后者仍然可以生成并返回多个： demo
SELECT id,(SELECT jsonb_path_query(data::jsonb,'$[*]?(@.key=="2").value')LIMIT 1)
FROM example;
SELECT id, values
FROM example LEFT JOIN jsonb_path_query(data::jsonb, '$[*]?(@.key=="2").value')
AS srf(values)
ON true;
第 2 行在该函数中没有生成任何行，这就是您没有看到它的原因 - 没有要null的行。选择部分不是 SRF 的正常位置 - 当应用在那里时，它最终会被推到要inner joinedfrom部分。而inner join空集，就是空集。这一点，再加上隐式的加入beahaviour，这就是为什么你可以阅读：
SELECT srf();作为实际SELECT b.x FROM srf() AS b(x);SELECT a.id, srf() FROM a;含义SELECT a.id, b.x FROM a, srf() AS b(x);SELECT a.id, b.x FROM a, srf(a.v) AS b(x);和SELECT a.id, srf(a.v) FROM a;实际上的意思是：SELECT a.id, b.x FROM a INNER JOIN srf(a.v) AS b(x) ON true;
这INNER JOIN抛弃了您没有产生结果dataid。LEFT JOIN不会。
另一个插图，带有更明显generate_series()"给我这么多行" SRF：演示
create table table1(id smallserial, how_many_to_generate int);
insert into table1(how_many_to_generate) values (1),(0),(2),(0),(1);
--no rows for the 2nd and 4th entry where `how_many_to_generate` is 0
--on the other hand, the 3rd entry requested and received two rows
select id, how_many_to_generate, generate_series(1, how_many_to_generate)
from table1;
-- id | how_many_to_generate | generate_series
------+----------------------+-----------------
-- 1 | 1 | 1
-- 3 | 2 | 1
-- 3 | 2 | 2
-- 5 | 1 | 1
如果你想要路径表达式的结果，你需要jsonb_path_query_first()：
SELECT id,
jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') AS values
FROM example
请注意，这将返回一个jsonb值。如果需要text值，请使用：
jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') #>> '{}
根据PostgreSQL文档，过滤器充当WHERE条件
定义路径时，还可以使用一个或多个类似于 SQL 中的 WHERE 子句的筛选表达式。筛选器表达式以问号开头，并在括号中提供条件：
我设法使用LATERAL和LEFT JOIN实现了您想要的东西
SELECT id,
*
FROM example left join
LATERAL jsonb_path_query(
TO_JSONB(data),
'$[*] ? (@.key == "2").value'::JSONPATH)
on true;
结果
id | id | data | jsonb_path_query
----+----+----------------------------------------------------------------+------------------
1 | 1 | [{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}] | "val2"
2 | 2 | [{"key": "1", "value": "val1"}] |
3 | 3 | [{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}] | "val2"
(3 rows)
要解决原始问题：
。为什么省略
jsonb_path_query的空输出？
以及蒂姆-麦库拉赫的赏金中增加的问题：
。为什么在结果为 null 的情况下从结果中省略整行。
两者都建立在微妙不正确的假设之上，即存在"空输出"或"空结果"。但是，起初，实际上只是"没有行"。让一切变得不同。相关：
- 如果未找到记录，则返回一个值
jsonb_path_query()是一个集合返回函数("SRF">)。它不返回 1 个结果，而是返回0-n 个结果行。
标准 SQL 不允许在SELECT列表中使用 SRF，只允许在FROM子句中使用。但是Postgres(像其他一些RDBMS一样)允许它。传统上，这会导致奇怪的极端情况。确切的行为最终通过Postgres 10解决(消毒)。看：
- SELECT 子句中多个集合返回函数的预期行为是什么？
单个集合返回函数，如示例中所示：
SELECT id, jsonb_path_query(...) AS foo FROM example;
实际上几乎与1相同：
SELECT id, foo FROM example CROSS JOIN LATERAL jsonb_path_query(...) foo;
(隐式)连接消除了 SRF 为空的输入行("无行"！
OTOH 它将输入行相乘，其中 SRF 生成多个结果行。
手册中有一个示例在"SQL 函数返回集"一章中确切地解释了这种情况：
在最后
SELECT中，请注意Child2没有显示输出行，Child3等。发生这种情况是因为listchildren为这些参数返回一个空集，因此不会生成任何结果行。这是 与从内部连接到函数结果时获得的行为相同 使用LATERAL语法。PostgreSQL 在查询选择中的集合返回函数的行为 list 几乎与集合返回函数完全相同 改为写在
LATERAL FROM子句项中。例如SELECT x, generate_series(1,5) AS g FROM tab;几乎等同于
SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
粗体强调我的.
还有更多，包括上面"几乎">1的详细信息。阅读它。
溶液
直接与jsonb合作以消除投射json产生的噪音。
最初的问题忽略了 JSON 数组中的多个对象可能符合条件的事实。
若要获取与原始查询一样设置的所有匹配项，并保留不匹配的输入行，请将隐式CROSS JOIN替换为LEFT JOIN ... ON true。(然后将"无行"转换为null！
SELECT e.id
, d.val #>> '{}' AS any_value
FROM example e
LEFT JOIN LATERAL jsonb_path_query(e.data, '$[*] ? (@.key == "2").value') d(val) ON true;
看：
- PostgreSQL中的LATERAL JOIN和子查询有什么区别？
仅获取第一个匹配项，同时保留所有输入行：
SELECT id
, jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') #>> '{}' AS first_value
FROM example;
或者将所有匹配项作为 JSON 数组获取，同时保留所有输入行：
SELECT id
, jsonb_path_query_array(data, '$[*] ? (@.key == "2").value') AS all_values
FROM example;
小提琴- 添加了示例行，该行生成两个匹配项来传达要点。
正确转换为text
我的任何查询都使用相同的"技巧"将 JSON 值转换为已显示a_horsetext：#>> '{}'。以下是出色的解释：
JSON#>>运算符将指定路径处的 JSON 子对象提取为text。路径以 Postgres 文本数组的形式提供text[]。'{}'是一个空数组。所以我们得到根对象作为text.瞧。看：
- 提取 JSON 字符串内容的替代方法
将 JSON 数组转换为text[]稍微复杂一些：
SELECT e.id
, ARRAY(SELECT jsonb_array_elements_text(jsonb_path_query_array(e.data, '$[*] ? (@.key == "2").value'))) AS all_values_txt
, jsonb_array_to_text_array(jsonb_path_query_array(e.data, '$[*] ? (@.key == "2").value')) AS all_values_txt2
FROM example e;
两者都将 JSON 数组转换为 Postgrestext[]。第二个表达式将其封装在一个函数中，如下所述：
- 如何将JSON数组转换为Postgres数组？