Eric Herman added a comment - 2020-07-10 14:20 - edited MariaDB [test]> show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `foo_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `foo_dat` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`foo_dat`)), PRIMARY KEY (`foo_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 1 row in set (0.000 sec)   MariaDB [test]> SELECT * FROM foo; +--------+--------------------------+ | foo_id | foo_dat | +--------+--------------------------+ | 1 | {"A": 1, "C": 3, "B": 2} | | 2 | {"A": 7, "C": 9, "B": 8} | +--------+--------------------------+ 2 rows in set (0.000 sec)   MariaDB [test]> SELECT * FROM foo WHERE JSON_CONTAINS(foo_dat, '{"A": 1, "B": 2, "C": 3}'); +--------+--------------------------+ | foo_id | foo_dat | +--------+--------------------------+ | 1 | {"A": 1, "C": 3, "B": 2} | +--------+--------------------------+ 1 row in set (0.000 sec) Thank you @Kartik Soneji , this was non-obvious to me from the https://mariadb.com/kb/en/json-functions/ page. While JSON_CONTAINS can work, to avoid matching larger objects we probably want two comparisons, e.g.:: MariaDB [test]> SELECT foo_id FROM foo WHERE -> JSON_CONTAINS(foo_dat, '{"A": 1, "B": 2, "C": 3}') -> AND JSON_CONTAINS('{"A": 1, "B": 2, "C": 3}', foo_dat); +--------+ | foo_id | +--------+ | 1 | +--------+ 1 row in set (0.000 sec) I feel like a more explicit JSON_EQUALS is still desired. It looks like fairly common problem: https://stackoverflow.com/questions/59471116/mariadb-compare-two-json-objects https://stackoverflow.com/questions/46734547/compare-json-values-in-mariadb https://dba.stackexchange.com/questions/208481/mariadb-compare-json

Rodrigo Souza added a comment - 2020-10-03 22:40 @Daniel Black I wonder if would be OK for me to take this task? Do we have anybody to talk about it and get advices?

Roel Van de Paar added a comment - 2021-09-25 07:34 Logged MDEV-26678 JSON_EQUALS validates invalid JSON and valid JSON as being equal when using a backslash Also logged mostly unrelated MDEV-26679 JSON_VALID validates invalid JSON