comparing 2 mysql json datatype columns

Surprisingly, I cannot find much about comparing two JSON datatype fields in mySQL. It seems like folks always compare JSON field with a string being cast to a JSON object. Anyway, I'm hoping this isn't a bug and someone can tell me how to handle:

I have two tables.

CREATE TABLE 
    test 
    ( 
        p_id VARCHAR(40), 
        p_json json 
    ) ;

CREATE TABLE 
    test2 
    ( 
        p_id VARCHAR(40), 
        p_json json 
    ) ;

I put in a record into each table. This record looks different, but it is the same JSON object really, since the ordering of keys and values should not matter... Even in nested array's objects..

 insert into test values ('1',cast('{"recs": [{"priority": 2, "code": "204666"}, {"priority": 1, "code": "204624"}]}' as json));
 insert into test2 values ('1',cast('{"recs": [{"priority": 1, "code": "204624"}, {"priority": 2, "code": "204666"}]}' as json));

Now when i try and compare these two json records, they are NOT treated as the same object..

select t.*
from test t
join test2 t2 on t.p_json = t2.p_json
 ;

But no records are returned. I would expect the record is returned since the objects are actually the same as far as json objects go... right?

My json object is much more complicated with many nested arrays, etc.. so I really hope nobody suggests I need to break down the object and compare sub objects/arrays and values separately.

Solution

They are not the same JSON document.

JSON object keys are unordered, and MySQL normalizes them.

mysql> select cast('{"priority": 2, "code": "204666"}' as json)
            = cast('{"code": "204666", "priority": 2}' as json) 
            as json_objects_are_equal;
+------------------------+
| json_objects_are_equal |
+------------------------+
|                      1 |
+------------------------+

But JSON arrays are ordered. That is, [0] means the first element in the array, [1] is the second element of the array, etc.

mysql> select cast('[1, 2]' as json) 
            = cast('[2, 1]' as json)
            as json_arrays_are_equal;
+-----------------------+
| json_arrays_are_equal |
+-----------------------+
|                     0 |
+-----------------------+

I can't suggest you break down the arrays, because I don't know what you're trying to do. But you should be aware of the behavior of JSON, and write your queries accordingly.

https://dev.mysql.com/doc/refman/8.2/en/json.html#json-comparison says:

Two JSON arrays are equal if they have the same length and values in corresponding positions in the arrays are equal.

(emphasis mine)