JSON数据类型上的UNION ALL [英] UNION ALL on JSON data type

问题描述

我需要在Postgres 9.2中的 JSON 列中 UNION ALL .但是Postgres会回复此错误:

I need to UNION ALL a JSON column in Postgres 9.2. But Postgres replies with this error:

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 数据类型的相等运算符.
如果正确，为什么?

It seems that Postgres doesn't have an equality operator for the json data type.
If this is correct, why?

作为试图找出问题的示例，它可以正常工作:

As an example trying to figure out the problem, this works fine:

(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 消除重复的值.

Note, UNION ALL just "adds" results, as opposed to just UNION which eliminates duplicate values.

推荐答案

测试JSON值是否相等"并非易事.除其他事项外，属性可以按任何顺序排序，或者可以有任意数量的无关紧要的空白.因此，根据JSON规范，值的二进制或文本表示形式可以完全不同.这就是为什么没有相等运算符用于数据类型在PostgreSQL中 json .

It is not trivial to test whether JSON values are "equal". Among other things, attributes can be sorted in any order, or there can be any amount of insignificant white space. So the binary or text representation can be completely different while the value still qualifies as equal according to JSON specifications. That's why there is no equality operator for the data type json in PostgreSQL.

如果您对文本表示形式相等感到满意(如示例所示)，则可以将 UNION ALL 与 text 列一起使用，并转换为 json以后:

If you are satisfied with the text representations being equal (as it seems from the example) you could UNION ALL with a text column and cast to json later:

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 ，它带有以前缺少的等式运算符(以及其他功能).参见:

Or you can use jsonb in Postgres 9.4 or later, which comes with the previously missing equality operator (among other things). See:

• 如何从PostgreSQL中的JSON []数组中删除已知元素?
• 从同一张表的两个视图中选择UNION

然后考虑以下替代查询:

Then consider this alternative query:

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 .

In addition to UNION, ORDER BY is also possible now.

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

Note the use of to_jsonb(). By feeding it a row, column name(s) are used as key names automatically. This is cleaner and faster and (among other things) preserves possible numeric types, which can affect equality and sort order. (to_json() is also available.)

db<>小提琴此处
_{Old sqlfiddle}

这篇关于JSON数据类型上的UNION ALL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！