How to compare two JSON fields in MariaDB?

MariaDB 10.2 and 10.3 - FAILS


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 OK

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.

  • 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. – Vladislav Vaintroub 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

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.

  • 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

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.