比较 MariaDB 中的 JSON 值 [英] Compare JSON values in MariaDB

问题描述

如何比较 MariaDB 中的两个 JSON 值?两个值，例如 {b": 1, a": 2} 和 {a": 2, b": 1} 应该等于.MariaDB 是否包含重新排序 JSON 值元素的功能?

How can I compare two JSON values in MariaDB? Two values such as {"b": 1, "a": 2} and {"a": 2, "b": 1} should be equal. Does MariaDB contain function to reorder elements of a JSON value?

推荐答案

只要使用 JSON_EXTRACT，JSON_EXTRACT 不关心数字在 JSON 字符串中的位置.

Just use JSON_EXTRACT, JSON_EXTRACT doesnt care about the position of a digit within a JSON string.

查询

  SELECT
      JSON_EXTRACT(@json_string_1, '$.a') AS a1
    , JSON_EXTRACT(@json_string_2, '$.a') AS a2
    , JSON_EXTRACT(@json_string_1, '$.b') AS b1
    , JSON_EXTRACT(@json_string_2, '$.b') AS b2
  FROM (
    SELECT 
       @json_string_1 := '{"b":1,"a":2}'
     , @json_string_2 := '{"a":2,"b":1}'
  ) 
   AS
    json_strings

结果

a1      a2      b1      b2      
------  ------  ------  --------
2       2       1       1       

现在将此结果用作交付表，以便我们检查 a1 是否等于 a2 以及 b1 是否等于 b2.

Now use this result as delivered table so we can check if a1 is equal to a2 and b1 is equal to b2.

查询

SELECT 
 1 AS json_equal
FROM ( 

  SELECT
      JSON_EXTRACT(@json_string_1, '$.a') AS a1
    , JSON_EXTRACT(@json_string_2, '$.a') AS a2
    , JSON_EXTRACT(@json_string_1, '$.b') AS b1
    , JSON_EXTRACT(@json_string_2, '$.b') AS b2
  FROM (
    SELECT 
       @json_string_1 := '{"b":1,"a":2}'
     , @json_string_2 := '{"a":2,"b":1}'
  ) 
   AS
    json_strings
) 
 AS json_data
WHERE
   json_data.a1 = json_data.a2
 AND
   json_data.b1 = json_data.b2 

结果

json_equal  
------------
           1

这篇关于比较 MariaDB 中的 JSON 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！