主页 / dba / 问题 / 95778
Accepted
Kokizzu
Asked: 2015-03-21 02:22:57 +0800 CST

对 JSONB_EACH 结果的 UNION ALL 的说明

  • 772

该函数用于合并2 JSONB，并在值为null时删除某个key，参考这个问题。

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE key NOT IN (SELECT key FROM json_union WHERE value ='null');
$$ LANGUAGE SQL;

第一个问题，为什么value = 'null'有效，但value IS NULL不是？

第二个问题WHERE value <> 'null'，使用instead of有什么不好的理由WHERE key NOT IN (SELECT key FROM ...)吗？

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> 'null';
$$ LANGUAGE SQL;

第三个问题，为什么这个函数也不正确（总是返回NULL）？是因为 UNION ALL 将所有结果记录更改为TEXT吗？

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> (NULL::JSONB)
$$ LANGUAGE SQL;

-- check function:
SELECT COALESCE(jsonb_merge('{}','{"b":null}'),'{"x":"its empty"}'::JSONB) FROM xxx;
      coalesce      
--------------------
 {"x": "its empty"}
postgresql postgresql-9.4

1 个回答

  1. Best Answer

    问题 1
    来自 PostgreSQL 文档表 8.23 - JSON 原始类型和相应的 PostgreSQL 类型：

    JSON primitive type | PostgreSQL type | Notes
null                |(none)           | SQL NULL is a different concept

    因此，您不能将 SQLNULL值与JSONnull 类型混淆。

    问题 2
    在您之前的问题中，您希望 JSON 值 null 充当一种 DELETE 类型。如果您同时拥有{"b": 3}并且{"b": null}在UNION并且您这样做value <> 'null'了，{"b": 3}则不会被删除。当您这样做时NOT IN，该对b:3也将被排除在新集合之外（在子查询中为键“b”找到空类型）。

    问题3
    与问题1相关：NULL::JSONB表示数据类型为JSONB的SQL NULL值，不是JSON null类型。

    • 3

相关问题