MySQL-如何比较两个Json对象? [英] Mysql - How to compare two Json objects?

问题描述

将整个MySql json列与json对象进行比较的语法是什么?

What is the syntax to compare an entire MySql json column with a json object?

以下内容无效:

select count(criteria) from my_alerts where criteria = '{"industries": ["1"], "locations": ["1", "2"]}'

即使条件列的值为{"industries": ["1"], "locations": ["1", "2"]}

如果我错了，请纠正我，但是如果两个JSON对象具有相同的键集，并且两个对象中的每个键具有相同的值，则它们相等.键和值的顺序将被忽略.所以下面应该是相同的吗?

correct me if I'm wrong but two JSON objects are equal if they have the same set of keys, and each key has the same value in both objects. The order of the keys and values is ignored. So the following should be the same?

 {"industries": ["1"], "locations": ["1", "2"]} = {"locations": ["2", "1"], "industries": ["1"]}

*更新*

我设法通过强制转换为json使其工作:

I've managed to get it working by casting to json as follows:

select count(criteria) from my_alerts where criteria = CAST('{"industries": ["1"], "locations": ["1", "2"]}' AS JSON)

但是，尽管在比较过程中忽略了键的顺序，但仍会比较值的顺序.所以以下是虚假的:

However whilst the order of the keys is ignored during the comparison the order of the values is still compared. So the following is falsy:

{"locations": ["1", "2"]} = {"locations": ["2", "1"]}

有没有办法强制比较也忽略值的顺序?

Is there any way to force the comparison to ignore order of the values aswell?

推荐答案

您可以使用JSON_CONTAINS:

SELECT COUNT(criteria) 
FROM my_alerts 
WHERE JSON_CONTAINS(criteria,'{"industries": ["1"], "locations": ["1", "2"]}')

此操作执行的比较将忽略值的顺序，这很关键，因为MySQL会对JSON属性进行重新排序以提高INSERT的效率.

This perform a comparison that ignores the order of the values, which is critical because MySQL will re-order JSON properties for efficiency on INSERT.

这篇关于MySQL-如何比较两个Json对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！