Compare JSON In MySql

Scenario

I have a small need where I have to compare 2 JSON Records in MySql. The query will check both JSONs and will return some count if there is any modification in the new JSON record.

To help you understand easily, I am adding small JSON although I have some large JSON to compare.

To start, first create a test table.

CREATE TABLE `table1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `json_col` json DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

To insert dummy JSON record, please use Insert query.

INSERT INTO `iaptsdb`.`table1`
(`json_col`)
VALUES('{"Unit": "109", "LastName": "Smith", "PersonId": "t0077508", "FirstName": "Karleen"}');

Now you have one record in table1.

Check by selecting this table1.

Now we will add our JSON Compare query to check JSON with above record.

We will update FirstName in new JSON and then we will compare it with above record.

I have SET FirstName to "John" and compare it to Record Id 1 in table1.

As FirstName is not equal to FirstName Record Id 1, hence it will reflect no record.**

set @some_json = '{"Unit": "109", "LastName": "Smith", "PersonId": "t0077508", "FirstName": "John"}';
select * from  table1
 WHERE
 CAST(json_col as JSON) = CAST(@some_json as JSON);

If we add "Karleen" as FirstName in JSON, the query will compare and as record is same, so it reflect one record.

P.S: Sequence in JSON Key:Value doesn't matter. :)

If you have implemented other approaches, please share them in the comments as well.