MariaDB: How to compare JSON values?

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.

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.

StackExchange Database Administrators Q#208481, answer score: 3