3

How to compare JSON values in MariaDB?

MariaDB 10.2 and 10.3 - Fails:

SELECT CAST('{"q":2}' AS JSON);

Returns:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON)' at line 1

MySQL 5.7.19 - Works:

SELECT CAST('{"num": 1.1, "date": "2015-11-01"}' AS JSON) = CAST('{"date": "2015-11-01", "num": 1.1}' AS JSON);

Returns: 1

Source: http://rpbouman.blogspot.com/2015/11/mysql-few-observations-on-json-type.html

Other similar questions

I found this but my JSON is more complex.

3
  • If you sort keys, recursively, on the client side, before your store, you can compare JSON_COMPACT(a) to JSON_COMPACT(b) . If you also remove spaces, before you send to server, you can just compare strings. Jun 2 '18 at 12:10
  • I know. That was my fallback idea too. :) I tried JSON_COMPACT - it works ok.
    – hrvoj3e
    Jun 3 '18 at 17:15
  • 1
    cf jira.mariadb.org/browse/MDEV-11439
    – Rick James
    Jun 5 '18 at 21:45
2

There is not, as of 10.3.7, a native JSON type on mariadb server. JSON is only an alias for LONGBLOB for compatibility reasons with MySQL: https://mariadb.com/kb/en/library/json-data-type/

While there is a library of function to work with JSON https://mariadb.com/kb/en/library/json-functions/ (select and transform it); there isn't really a native type, so all input and output of those functions is really of type string. While you could technically implement a recursive json parser with a stored procedure, I would recommend to just read it and compare it with client libraries or other client utilities (for example, https://stackoverflow.com/questions/25851183/how-to-compare-two-json-objects-with-the-same-elements-in-a-different-order-equa ).

Despite the claims by MariaDB that it is a drop-in replacement of MySQL, because MariaDB is no longer a 100% compatible fork, functionalities that are available on one software are no longer available on the other and viceversa.

2
  • Too bad for MariaDB. It looks like it must be done on client side. At least for now (v10.3).
    – hrvoj3e
    Jun 3 '18 at 17:17
  • @hrvoj3e If you are happy with the answer (even if that makes you unhappy), you can accept it as valid, thanks.
    – jynus
    Jun 6 '18 at 8:03
3

Disclaimer: I work for MariaDB

As of 10.7 you will be able to use the new functions JSON_EQUALS and JSON_NORMALIZE. See related tickets/implementation at:

MariaDB-10.7>SELECT JSON_NORMALIZE('{"A": 1, "C": 3, "B": 2}');
+--------------------------------------------+
| JSON_NORMALIZE('{"A": 1, "C": 3, "B": 2}') |
+--------------------------------------------+
| {"A":1.0E0,"B":2.0E0,"C":3.0E0}            |
+--------------------------------------------+
1 row in set (0.001 sec)
MariaDB-10.7>SELECT JSON_EQUALS('{"A": 1, "C": 3, "B": 2}','{"A":1.0E0,"B":2.0E0,"C":3.0E0}');
+---------------------------------------------------------------------------+
| JSON_EQUALS('{"A": 1, "C": 3, "B": 2}','{"A":1.0E0,"B":2.0E0,"C":3.0E0}') |
+---------------------------------------------------------------------------+
|                                                                         1 |
+---------------------------------------------------------------------------+
1 row in set (0.001 sec)
0
1

From MariaDB 10.2.3 you can use JSON_CONTAINS.

SELECT JSON_CONTAINS(
    '{"num": 1.1, "date": "2015-11-01"}'
    , '{"date": "2015-11-01", "num": 1.1}');

Returns: 1

However, this will also return 1:

SELECT JSON_CONTAINS(
    '{"num": 1.1, "date": "2015-11-01", "Extra":"My extrafield"}'
    , '{"date": "2015-11-01", "num": 1.1}');

So to see if the contains the same data, you can use:

SET @json1 := '{ "num": 1.1, "date": "2015-11-01"}';
SET @json2 := '{"date": "2015-11-01", "num": 1.1}';

SELECT JSON_CONTAINS(@json1, @json2) AND JSON_CONTAINS(@json2, @json1);

Returns: 1

Be aware that lists doesn't have to be equal, just contain the same elements.

SET @json1 := '{ "num": 1.1, "date": "2015-11-01", "List":[1,2,2,2,2] }';
SET @json2 := '{"List":[2,1], "date": "2015-11-01", "num": 1.1}';

SELECT JSON_CONTAINS(@json1, @json2) AND JSON_CONTAINS(@json2, @json1);

Returns: 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.