比较MariaDB中的JSON值

问题描述：

如何在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值

相关推荐