json_array_elements with null values

Question

我在Windows上使用PostgreSQL 9.4.5,64位。 我有一些不规则大小的数组。我想使用json_array_elements展开类似于以下代码的数组

with outside as (select (json_array_elements('[[],[11],[21,22,23]]'::json)) aa, json_array_elements('[1,2,3]'::json)bb) 
select json_array_elements_text(aa), bb from outside

但是，当我运行这个时，我得到

aa | bb
------- 
11 |  2
21 |  3
22 |  3
23 |  3

aa 列中的空数组与 bb 列中的值 1 一起放在地板上

我想得到

aa   | bb
---------- 
null |  1
11   |  2
21   |  3
22   |  3
23   |  3

另外，这是PostgreSQL中的一个错误吗？

Answer 1

您使用了正确的函数，但使用了错误的JOIN。如果您(可能)在JOIN的一侧没有行，并且希望保留JOIN另一侧的行并使用NULLs来"填充"行，则需要OUTER JOIN：

with outside as (
select json_array_elements('[[],[11],[21,22,23]]') aa,
json_array_elements('[1,2,3]') bb
) 
select    a, bb
from      outside
left join json_array_elements_text(aa) a on true

注意：将on true视为连接条件似乎很奇怪，但实际上当您使用LATERAL连接时，它实际上是非常通用的(当您直接在FROM子句中使用集合返回函数 (SRF) 时，这是隐式的)。

http://rextester.com/KNW13145

编辑：您的原始查询不直接涉及JOIN，但更糟：您在SELECT子句中使用了 SRF。这几乎就像一个CROSS JOIN，但实际上它有自己的规则。不要使用它，除非你确切地知道你在做什么以及为什么需要它。

Answer 2

这不是一个错误。json_array_elements_text('[null]')返回null，json_array_elements_text('[]')不返回任何内容。

with outside as (
select (
json_array_elements('[[],[11],[21,22,23]]'::json)) aa, 
json_array_elements('[1,2,3]'::json) bb
) 
select elem as aa, bb
from outside,
json_array_elements_text(case when aa::text = '[]' then '[null]'::json else aa end) elem;
aa | bb 
----+----
| 1
11 | 2
21 | 3
22 | 3
23 | 3
(5 rows)    

Answer 3

解决我自己的问题，我有一个可能的答案，但似乎一团糟

With initial as (select '[[],[11],[21,22,23]]'::json as a, '[1,2,3]'::json as b),
Q1 as (select json_array_elements(a) as aa, json_array_elements(b) bb from initial),
Q2 as (select ARRAY[aa->>0, aa->>1, aa->>2] as aaa, bb as bbb, ARRAY[0,1,2] as ccc from q1), 
-- where the indicices are computed in a separate query by looping from 0 to json_array_length(...)
Q3 as (select unnest(aaa) as aaaa, bbb as bbbb, unnest(ccc) as cccc from q2)
Select aaaa, bbbb from q3 where aaaa is not null or cccc = 0