json - postgresql json_object_keys(var1) 剔除var1为null的记录

Question

我正在尝试 postgresql 9.3.4 中的一些 JSON 功能。

我在使用 json_object_keys(var1) 时遇到问题，它删除了 var1 为空的行。

我如何调整查询以保留这些记录，在这种情况下填充 null

比如我有一张表test1:

postgres=# select * from test1;
 var1 |       var2        
------+-------------------
    1 | {"a": 20, "b":30}
    2 | 

postgres=# select var1,json_object_keys(var2) from test1;
 var1 | json_object_keys 
------+------------------
    1 | a
    1 | b

我希望输出是这样的:

postgres=# select var1, json_object_keys(coalesce(var2,'{"": null}')) from test1;
 var1 | json_object_keys 
------+------------------
    1 | a
    1 | b
    2 | 

只是想知道是否有更好的方法来做到这一点？

Answer 1

不要使用 PostgreSQL 对 SELECT 列表中的集合返回函数的古怪支持，而是使用横向查询。

我最初编写的不正确查询:

select t.var1, k from test1 t, json_object_keys(t.var2) k;

但那是错误的，因为隐式 lateral 是交叉连接，零行的交叉连接仍然是零行。

你想要:

SELECT t.var1, k
FROM test1 t 
LEFT JOIN LATERAL (
  SELECT t.var1, k 
  FROM json_object_keys(t.var2) k
) lj(var1, k) 
ON (t.var1 = lj.var1);

例如

regress=> CREATE TABLE test1 ( var1 integer, var2 json );
CREATE TABLE
regress=> INSERT INTO test1 (var1, var2) VALUES (1, '{"a": 20, "b":30}'), (2, NULL);
INSERT 0 2
regress=>   SELECT t.var1, k
regress->     FROM test1 t 
regress->     LEFT JOIN LATERAL (
regress(>       SELECT t.var1, k 
regress(>       FROM json_object_keys(t.var2) k
regress(>     ) lj(var1, k) 
regress->     ON (t.var1 = lj.var1);
 var1 | k 
------+---
    1 | a
    1 | b
    2 | 
(3 rows)

它为每个 json 字段评估 json_object_keys，然后将结果与输入进行左连接。