sql - 在 SELECT 列表中两次使用 jsonb_array_elements_text() 的结果不一致

Question

为什么当数组中的元素数量发生变化时，下面查询的行为会发生变化？

以下代码片段在同一查询中扩展了两个数组并具有两种不同的行为:

• 当两个数组的元素个数相同时，每行一行返回元素
• 当两个数组的个数不同时元素，它的行为类似于 CROSS JOIN

所有这些都在 Postgres 9.5.2 中执行:

CREATE TABLE test(a text, b jsonb, c jsonb);

INSERT INTO test VALUES
('A', '["b1","b2"]', '["c1","c2"]'),
('B', '["b1","b2"]', '["c1","c2","c3"]');

SELECT a, jsonb_array_elements_text(b) b, jsonb_array_elements_text(c) c
FROM test;

结果如下:

A  b1  c1
A  b2  c2
B  b1  c1
B  b2  c2
B  b1  c3
B  b2  c1
B  b1  c2
B  b2  c3

这是我所期望的:

A  b1  c1
A  b1  c2
A  b2  c1
A  b2  c2
B  b1  c1
B  b2  c2
B  b1  c3
B  b2  c1
B  b1  c2
B  b2  c3

Answer 1

在 SELECT 列表中组合多个返回集合的函数不符合 SQL 标准，其中所有返回集合的元素都进入 FROM 列表。您可以在 Postgres 中执行此操作，但它在版本 10 之前表现出令人惊讶的行为，最终在版本 10 中对其进行了清理。

所有这些都与数据类型 jsonb 或函数 jsonb_array_elements_text() 没有直接关系 - 除了它是一个返回集合的函数。

如果您想要笛卡尔积，可靠且不依赖于您的 Postgres 版本，请改用 CROSS JOIN LATERAL(至少需要 Postgres 9.3):

SELECT t.a, jb.b, jc.c
FROM   test t
     , jsonb_array_elements_text(t.b) jb(b)
     , jsonb_array_elements_text(t.c) jc(c)
ORDER  BY t.a, ???;  -- your desired order seems arbitrary beyond a

FROM 列表 (,) 中的逗号基本上是 CROSS JOIN LATERAL 的简短语法。

参见:

• What is the difference between LATERAL and a subquery in PostgreSQL?

您实际问题的解释:

Why does the behavior of the query below change when the number of elements in the array changes?

• What is the expected behaviour for multiple set-returning functions in SELECT clause?