So sánh và Sắp xếp các giá trị JSON

So sánh và Sắp xếp các giá trị JSON

Tiếp tục phần tìm hiểu về kiểu giá trị JSON trong mySQL, bài viết này bàn luận về vấn đề So sánh và Sắp xếp các giá trị JSON.

Giá trị JSON có thể được so sánh bằng cách sử dụng các toán tử =, <, <=, >, >=, <>, != và <=>. Các toán tử so sánh và function chưa được hỗ trợ với giá trị JSON là: BETWEEN, IN(), GREATEST() và LEAST().

Một cách giải quyết cho các toán tử so sánh và chức năng được liệt kê ở trên là ép kiểu giá trị JSON sang kiểu MySQL số hoặc chuỗi để chúng có một kiểu JSON phi vô hướng thích hợp. So sánh các giá trị JSON diễn ra ở hai cấp độ. Mức đầu tiên so sánh dựa trên các kiểu JSON của các giá trị so sánh. Nếu các kiểu khác nhau, kết quả so sánh được xác định chỉ bằng kiểu có độ ưu tiên cao. Nếu hai giá trị có cùng kiểu JSON, cấp độ thứ hai của so sánh xảy ra sử dụng từng quy tắc với các kiểu cụ thể. Danh sách sau đây cho thấy độ ưu tiên của các kiểu JSON, từ ưu tiên cao nhất đến thấp nhất (các tên kiểu là những giá trị trả về của function JSON_TYPE()). Bất kỳ giá trị có một kiểu JSON liệt kê phía trước khi so sánh sẽ lớn hơn so với bất kỳ giá trị có một kiểu JSON liệt kê sau trong danh sách.

BLOB > BIT > OPAQUE > DATETIME > TIME > DATE > BOOLEAN > ARRAY > OBJECT > STRING > INTEGER = DOUBLE > NULL.

Đối với các giá trị của JSON cùng độ ưu tiên, các quy tắc so sánh tùy từng kiểu cụ thể như sau:

• BLOB hoặc BIT: N byte đầu tiên của hai giá trị được so sánh, mà N là số lượng byte của giá trị ngắn hơn. Nếu N byte đầu tiên của hai giá trị là giống hệt nhau, giá trị ngắn hơn được sắp xếp trước giá trị dài hơn.

• OPAQUE: Quy tắc tương tự như cho BLOB. Giá trị OPAQUE là những giá trị mà không được phân loại vào một trong những loại khác.

• DATETIME: Một giá trị đại diện cho một thời điểm trước đây được đặt trước một giá trị đại diện cho một điểm sau đó trong thời gian. Nếu hai giá trị ban đầu đến từ các loại MySQL DATETIME và TIMESTAMP, tương ứng, chúng bằng nhau nếu đại diện cho cùng một thời điểm.

• TIME: Giá trị nhỏ hơn trong hai giá trị thời gian được đặt trước.

• DATE: Ngày sớm hơn được sắp xếp trước ngày gần đây.

• ARRAY: Hai mảng JSON bằng nhau nếu chúng có cùng độ dài và các giá trị ở các vị trí trong mảng tương ứng bằng nhau. Nếu các mảng không bằng nhau, thứ tự của chúng được xác định bởi các phần tử ở vị trí đầu tiên, nơi có một sự khác biệt. Các mảng với giá trị nhỏ hơn ở vị trí đó được sắp xếp lên trước. Nếu tất cả các giá trị của mảng ngắn hơn là bằng với giá trị tương ứng trong mảng dài hơn, mảng ngắn hơn được sắp xếp trước. Thí dụ: [] < ["a"] < ["ab"] < ["ab", "cd", "ef"] <["ab", "ef"].

• BOOLEAN: Các JSON false là nhỏ hơn so với JSON true.

• OBJECT: Hai object JSON bằng nhau nếu chúng có cùng một tập hợp các key, và mỗi key có giá trị như nhau trong cả hai object. Thí dụ: {"a": 1, "b": 2} = {"b": 2, "a": 1}

• STRING: Strings được sắp xếp từ vựng dựa vào N byte đầu tiên của sự diễn tả utf8mb4 của hai chuỗi được so sánh, mà N là độ dài của chuỗi ngắn hơn. Nếu N byte đầu tiên của hai chuỗi giống hệt nhau, các chuỗi ngắn hơn được coi là nhỏ hơn. Thí dụ: "a" < "ab" < "b" < "bc".

  Sự sắp xếp này là tương đương với việc sắp xếp của chuỗi SQL có collation utf8mb4_bin. Bởi vì utf8mb4_bin là một collation nhị phân, so sánh các giá trị JSON là trường hợp nhạy cảm: "A" < "a".

• INTEGER, DOUBLE: Giá trị JSON có thể chứa các con số chính xác giá trị và số gần đúng giá trị. Các quy tắc để so sánh các số trong phạm vi giá trị JSON khác đôi chút so với các quy tắc để so sánh kiểu số MySQL native:

  • Trong một so sánh giữa hai cột là kiểu số native MYSQL INT và DOUBLE tương ứng, nó biết rằng mọi sự so sánh liên quan đến một số integer và double, vì vậy các số integer được chuyển đổi thành double đối với tất cả các hàng. Đó là, con số chính xác giá trị được chuyển đổi thành con số gần đúng giá trị.
  • Mặt khác trong so sánh hai cột chứa số JSON, nó có thể không biết trước được liệu số sẽ là integer hay double. Để cung cấp các hành vi phù hợp nhất trên tất cả các hàng, MySQL convert số gần đúng giá trị sang số có giá trị chính xác. Kết quả sắp xếp là phù hợp và không mất độ chính xác của những con số có giá trị chính xác. Ví dụ với giá trị vô hướng 9223372036854775805, 9223372036854775806, 9223372036854775807 và 9.223372036854776e18, thứ tự là như sau: 9223372036854775805 < 9223372036854775806 < 9223372036854775807 < 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001

  Khi so sánh JSON mà sử dụng các quy tắc so sánh số non-JSON, so sánh không phù hợp có thể xảy ra. Các quy tắc so sánh MySQL cho số thông thường mang lại những kết quả:

  • So sánh Integer: 9223372036854775805 < 9223372036854775806 < 9223372036854775807
  • So sánh Double: 9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18

Khi so sánh bất kì giá trị JSON với SQL NULL, kết quả sẽ là UNKNOWN.

Để so sánh giá trị JSON và giá trị phi JSON, giá trị phi JSON được chuyển đổi sang JSON, sau đó các giá trị so sánh như mô tả trước đây. Bảng dưới đây cung cấp một bản tóm tắt các quy tắc mà MySQL sử dụng khi ép kiểu giữa các giá trị JSON và các giá trị kiểu khác:

ORDER BY và GROUP BY cho các giá trị JSON làm việc theo nguyên tắc sau:

• Thứ tự của các giá trị vô hướng JSON sử dụng các quy tắc tương tự như trong các cuộc thảo luận trước.
• Đối với sắp xếp tăng dần, đặt SQL NULL trước tất cả các giá trị JSON, bao gồm cả giá trị chữ JSON null; đối với sắp xếp giảm dần, đặt SQL NULL sau tất cả các giá trị JSON, bao gồm cả các chữ JSON null.
• Sắp xếp các key cho các giá trị JSON đang bị ràng buộc bởi giá trị của biến hệ thống max_sort_length, các key khác nhau chỉ sau max_sort_length byte đầu tiên được so sánh là bằng nhau.
• Sắp xếp các giá trị nonscalar hiện không được hỗ trợ và xảy ra warning.

Đối với sắp xếp, nó có thể có lợi trong việc CAST một JSON vô hướng sang một số kiểu MySQL native. Ví dụ, nếu một cột tên jdoc chứa các object JSON có một phần tử bao gồm một khóa id và giá trị không âm, sử dụng biểu thức sau đây để sắp xếp theo giá trị id:

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)