MySQL中内容等价的JSON字段比较不相等问题求助

问题原因 #

MySQL中直接使用=运算符比较JSON类型字段时，会严格匹配JSON的存储序列化形式：

• 对于JSON对象，键的顺序不同会被判定为不等，但根据JSON规范，对象键的顺序不影响语义等价性；
• 对于JSON数组，元素的顺序属于JSON结构的一部分，顺序不同会被视为不同的JSON值，不过部分业务场景中可能需要忽略数组元素的顺序来判断等价。

你的示例中，两张表的p_json字段包含的数组元素顺序相反，因此直接用=比较会判定为不等，查询无结果。

解决方案 #

1. 忽略对象键顺序的等价比较（MySQL 8.0.17+） #

使用MySQL内置的JSON_EQUAL()函数，它会忽略JSON对象的键顺序，仅判断语义等价性：

select t.*
from test t
join test2 t2 on JSON_EQUAL(t.p_json, t2.p_json);

注意：该函数不会忽略数组元素的顺序（JSON数组本身是有序结构），因此你的示例中用这个函数仍然不会返回结果，需要结合数组排序处理。

2. 忽略数组元素顺序的等价比较 #

如果业务中需要认为数组内元素顺序不影响等价性，可以通过以下方式处理：

方法1：排序数组元素后再比较

以你的示例为例，提取recs数组并按priority字段排序，重新生成JSON后再进行比较：

select t.*
from test t
join test2 t2 on 
  JSON_SET(t.p_json, '$.recs', JSON_ARRAYAGG(j.obj ORDER BY j.obj->>'$.priority'))
  =
  JSON_SET(t2.p_json, '$.recs', JSON_ARRAYAGG(j2.obj ORDER BY j2.obj->>'$.priority'))
join JSON_TABLE(t.p_json->>'$.recs', '$[*]' COLUMNS(obj JSON PATH '$')) j
join JSON_TABLE(t2.p_json->>'$.recs', '$[*]' COLUMNS(obj JSON PATH '$')) j2
group by t.p_id, t.p_json, t2.p_id, t2.p_json;

该方法通过JSON_TABLE拆分数组为行数据，按指定字段排序后用JSON_ARRAYAGG重新聚合为有序数组，替换原JSON中的数组部分后再比较。

方法2：生成哈希值比较

如果JSON结构复杂，可将排序后的JSON转换为字符串并生成哈希值（如SHA2()），通过比较哈希值判断等价性，性能更优：

select t.*
from test t
join test2 t2 on 
  SHA2(
    JSON_SET(t.p_json, '$.recs', JSON_ARRAYAGG(j.obj ORDER BY j.obj->>'$.priority'))
    ->>'$',
    256
  )
  =
  SHA2(
    JSON_SET(t2.p_json, '$.recs', JSON_ARRAYAGG(j2.obj ORDER BY j2.obj->>'$.priority'))
    ->>'$',
    256
  )
join JSON_TABLE(t.p_json->>'$.recs', '$[*]' COLUMNS(obj JSON PATH '$')) j
join JSON_TABLE(t2.p_json->>'$.recs', '$[*]' COLUMNS(obj JSON PATH '$')) j2
group by t.p_id, t.p_json, t2.p_id, t2.p_json;

3. 低版本MySQL兼容方案（8.0.17以下） #

若MySQL版本低于8.0.17，无JSON_EQUAL()函数，可将JSON转换为排序后的字符串再比较。以处理对象键顺序为例：

select t.*
from test t
join test2 t2 on 
  (select concat('{', group_concat(concat('"',`key`,'":',json_extract(t.p_json,concat('$.',`key`))) order by `key`), '}') 
   from JSON_TABLE(JSON_KEYS(t.p_json), '$[*]' COLUMNS(`key` VARCHAR(255) PATH '$')) k)
  =
  (select concat('{', group_concat(concat('"',`key`,'":',json_extract(t2.p_json,concat('$.',`key`))) order by `key`), '}') 
   from JSON_TABLE(JSON_KEYS(t2.p_json), '$[*]' COLUMNS(`key` VARCHAR(255) PATH '$')) k2);

该方法需根据JSON的具体结构编写字符串生成逻辑，适合结构固定的JSON。

内容的提问来源于stack exchange，提问作者da Bich