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

问题描述

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

<块引用>

错误:无法识别 json SQL 类型的相等运算符状态:42883字符:9

到查询:

(select cast('{"billingcode" : "' || billingcode || '"}' as JSON)从 billing_2012_08 限制 10)联盟(select cast('{"charged" : "' ||charged || '"}' as JSON)从 send_response_2012_08 限制 10)

这里有什么问题?

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

作为一个试图找出问题的例子，这很好用:

(select cast('{"billingcode" : "' || billingcode || '"}' as JSON)从 billing_2012_08 限制 10)联合所有(select cast('{"charged" : "' ||charged || '"}' as JSON)从 send_response_2012_08 限制 10)

注意，UNION ALL 只是添加"结果，而 UNION 只是消除重复值.

解决方案

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

如果您对文本表示相等(从示例中看起来)感到满意，您可以 UNION ALL 使用 text 列并转换为 json 稍后:

选择 json_col::json从 ((SELECT '{"billingcode" : "' || billingcode || '"}'::text AS json_colFROM billing_2012_08 LIMIT 10)联合所有(SELECT '{"charged" :"' || 收费 || '"}'::text从发送_响应_2012_08 限制 10)) 子

或者你可以使用jsonb，它带有以前缺少的相等运算符(以及其他内容).见:

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

然后考虑这个替代查询:

选择 to_jsonb(t) AS jsonb_colFROM (SELECT billingcode FROM billing_2012_08 LIMIT 10) t联盟SELECT to_jsonb(t) - 也保留可能的数字类型！FROM(从发送响应_2012_08 LIMIT 10 收取的选择)t按 1 排序；-- 可以使用 jsonb

除了UNION，ORDER BY现在也可以了.

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

db<>小提琴这里
_{旧版sqlfiddle}

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

To the query:

(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)

What's wrong here?

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)

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

解决方案

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.

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

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

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

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

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

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<>fiddle here
_{Old sqlfiddle}

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