json - JSON 数据类型上的 UNION ALL

Question

我需要 UNION ALL Postgres 9.2 中的 JSON 列。但是 Postgres 回复了这个错误:

ERROR: could not identify an equality operator for type json SQL  
state: 42883  
Character: 9

查询:

(select cast('{"billingcode" : "' || billingcode || '"}' as JSON)
 from billing_2012_08 limit 10)
union
(select cast('{"charged" : "' || charged || '"}' as JSON)
 from sending_response_2012_08 limit 10)

这里有什么问题吗？

Postgres 似乎没有json 数据类型的相等运算符。
如果这是正确的，为什么？

作为一个试图找出问题的例子，这个工作正常:

(select cast('{"billingcode" : "' || billingcode || '"}' as JSON)
 from billing_2012_08 limit 10)
union all
(select cast('{"charged" : "' || charged || '"}' as JSON)
 from sending_response_2012_08 limit 10)

请注意，UNION ALL 只是“添加”结果，而 UNION 只是消除重复值。

Answer 1

测试 JSON 值是否“相等”并非易事。除此之外，属性可以按任何顺序排序，或者可以有任意数量的无关紧要的空白。因此，二进制或文本表示形式可以完全不同，而 value 仍然符合 JSON 规范的要求。这就是为什么数据类型 json 没有没有相等运算符的原因在 PostgreSQL 中。

如果您对相等的文本表示感到满意(如示例所示)，您可以使用 text 列 UNION ALL 并转换为 json 之后:

SELECT json_col::json
FROM (
   (SELECT '{"billingcode" : "' || billingcode || '"}'::text AS json_col
    FROM   billing_2012_08 LIMIT 10)
   UNION ALL
   (SELECT '{"charged" : "' || charged || '"}'::text
    FROM   sending_response_2012_08 LIMIT 10)
   ) sub

或者您可以使用jsonb在 Postgres 9.4 或更高版本中，它带有以前缺少的相等运算符(除其他外)。见:

• How to remove known elements from a JSON[] array in PostgreSQL?
• SELECT UNION from two views of the same table

然后考虑这个替代查询:

SELECT to_jsonb(t) AS jsonb_col
FROM  (SELECT billingcode FROM billing_2012_08 LIMIT 10) t

UNION
SELECT to_jsonb(t)   -- also preserves possible numeric type!
FROM  (SELECT charged FROM sending_response_2012_08 LIMIT 10) t

ORDER  BY 1;  -- possible with jsonb

除了UNION，现在也可以使用ORDER BY。

注意 to_jsonb() 的使用.通过向它提供行，列名将自动用作键名。这更干净、更快，并且(除其他外)保留了可能影响相等性和排序顺序的可能的数字类型。 (to_json() 也可用。)

db<> fiddle here
<子>旧sqlfiddle