JSON trong MySql

Đến phiên bản MySQL 5.7.8, MySQL đã hỗ trợ kiểu dữ liệu JSON cho phép truy cập hiệu quả với dữ liệu trong tài liệu JSON. Kiểu dữ liệu JSON cung cấp những lợi thế hơn so với việc lưu trữ các chuỗi định dạng JSON trong một column string:

• Tự động validation tài liệu JSON được lưu trữ trong cột JSON. Các tài liệu không hợp lệ sẽ sinh ra lỗi.
• Định dạng lưu trữ được tối ưu hóa. Tài liệu JSON được lưu trữ trong các cột JSON được chuyển đổi sang một định dạng nội bộ cho phép truy cập nhanh chóng đến các phần tử tài liệu. Khi máy chủ phải đọc một giá trị JSON được lưu trữ ở định dạng nhị phân này, giá trị không cần phải được phân tích từ một đoạn text. Các định dạng nhị phân có cấu trúc cho phép các máy chủ tìm kiếm subobjects hoặc giá trị nested trực tiếp bởi key hoặc mảng index mà không cần đọc tất cả các giá trị trước hoặc sau chúng trong tài liệu.

Kích thước của tài liệu JSON được lưu trữ trong các cột JSON được giới hạn bằng giá trị của biến hệ thống max_allowed_packet (Trong khi các máy chủ thao tác một giá trị JSON trong nội bộ bộ nhớ trong, nó có thể lớn hơn; giới hạn được áp dụng khi các máy chủ lưu trữ nó). Các cột JSON không thể có một giá trị mặc định. Các cột JSON, giống như cột của các loại nhị phân khác, không được lập chỉ mục trực tiếp; thay vào đó, bạn có thể tạo một chỉ mục trên một cột được tạo ra bằng cách trích ra một giá trị vô hướng từ các cột JSON. Các bộ tối ưu của MySQL cũng sẽ tìm kiếm các chỉ số tương thích trên các cột ảo phù hợp với biểu thức JSON. MySQL Cluster NDB 7.5.2 và sau đó hỗ trợ các cột JSON và các function MySQL JSON, bao gồm cả việc tạo ra các chỉ mục trên một cột được tạo ra từ cột JSON như là một cách giải quyết khác cho vấn đề không thể đánh index cột JSON. Tối đa 3 cột JSON cho mỗi bảng NDB được hỗ trợ.

Thảo luận sau đây bao gồm các chủ đề:

• Tạo giá trị JSON
• Normalization, Merging, và Autowrapping của các giá trị JSON
• Tìm kiếm và Sửa đổi giá trị JSON

Cùng với các kiểu dữ liệu JSON, có sẵn một tập hợp các function SQL để hoạt động trên các giá trị JSON, chẳng hạn như tạo, thao tác và tìm kiếm. Một tập hợp các function không gian để hoạt động trên các giá trị GeoJSON cũng có sẵn.

Tạo giá trị JSON

Một mảng JSON chứa một danh sách các giá trị cách nhau bởi dấu phẩy và đặt trong cặp ký tự [ và ]: ["abc", 10, null, true, false]. Một đối tượng JSON chứa một bộ key/value được phân cách bởi dấu phẩy và đặt trong cặp ký tự { và }: { "K1": "value", "k2": 10}.

Như các ví dụ minh họa, mảng JSON và các đối tượng có thể chứa các giá trị vô hướng đó là chuỗi hoặc số, JSON null, hoặc JSON boolean true hay false. Key trong đối tượng JSON phải là các chuỗi. Giá trị thời gian (date, time, hoặc datetime) cũng được phép: ["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"] Nesting được cho phép trong các phần tử của mảng JSON và các giá trị của đối tượng JSON:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]

{"k1": "value", "k2": [10, 20]}

Bạn cũng có thể có được giá trị JSON từ một số function được cung cấp bởi MySQL cho mục đích này cũng như bằng cách casting các giá trị của các kiểu khác thành kiểu JSON sử dụng CAST(value AS JSON). Các phần tiếp theo mô tả cách MySQL xử lý các giá trị JSON được cung cấp như là đầu vào.

Trong MySQL, giá trị JSON đều là các chuỗi. MySQL phân tích bất kỳ chuỗi được sử dụng trong một bối cảnh đòi hỏi một giá trị JSON, và sinh ra một lỗi nếu nó không thỏa mãn chuỗi JSON. Những bối cảnh bao đó gồm thêm một giá trị vào một cột có kiểu dữ liệu JSON và truyền một tham số đến một function chờ đợi một giá trị JSON, như các ví dụ sau đây: Chèn một giá trị vào cột JSON thành công nếu đó là một giá trị JSON hợp lệ, nhưng không thành công nếu nó không phải:

mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text: "Invalid value." at position 6 in value (or column) '[1, 2,'.

Vị trí cho "at position N" trong các thông báo lỗi như vậy là bắt đầu từ 0, nhưng cần được xem xét dấu hiệu gần đúng của nơi mà vấn đề ở một giá trị thực sự xảy ra. Function JSON_TYPE() nhận một tham số JSON và cố gắng để phân tích nó thành một giá trị JSON. Nó trả về kiểu JSON của giá trị nếu nó là hợp lệ nếu không sẽ sinh ra một lỗi:

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+

mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

MySQL xử lý chuỗi được sử dụng trong bối cảnh JSON bằng cách sử dụng charset utf8mb4 và collation utf8mb4_bin. Strings ở charset khác được chuyển đổi thành utf8mb4 khi cần thiết (Đối với các string ở charset ascii hoặc utf8, không cần thiết chuyển đổi vì ascii và utf8 là tập con của utf8mb4). Để thay thế cho các giá trị JSON sử dụng các chuỗi, tồn tại các function để tạo giá trị JSON từ các phần tử thành phần. JSON_ARRAY()nhận một danh sách (có thể rỗng) các giá trị và trả về một mảng JSON chứa những giá trị đó:

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT() nhận một danh sách (có thể rỗng) các cặp key/value và trả về một đối tượng JSON chứa những cặp giá trị đó:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON_MERGE() nhận vào hai hoặc nhiều tài liệu JSON và trả về kết quả kết hợp:

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+

giá trị JSON có thể được gán cho biến do người dùng định nghĩa:

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql>SELECT @j;
+ ------------------ +
| @j |
+ ------------------ +
| { "Key": "value"} |
+ ------------------ +

Tuy nhiên, biến do người dùng định nghĩa không thể là kiểu dữ liệu JSON, vì vậy mặc dù @j trong ví dụ trước trông giống như một giá trị JSON và có cùng một character set và collation như một giá trị JSON, nó không có kiểu dữ liệu JSON. Thay vào đó, kết quả từ JSON_OBJECT() được chuyển thành một chuỗi khi gán cho biến. Strings sản xuất bằng cách chuyển đổi các giá trị JSON có bộ ký tự của utf8mb4 và collation utf8mb4_bin:

mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | 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.

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

Trường hợp nhạy cảm cũng áp dụng cho các JSON null, true và false, chúng luôn luôn phải được viết bằng chữ thường:

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.

Normalization, Merging, and Autowrapping of JSON Values

Khi một chuỗi được phân tích và tìm thấy là một tài liệu JSON hợp lệ, nó cũng thực hiện normalization: các thành phần với các key lặp với một key được tìm thấy trước đó trong tài liệu được bỏ đi (thậm chí nếu các giá trị khác nhau). Các giá trị object sinh ra bởi function JSON_OBJECT() sau đây không bao gồm phần tử key1 thứ hai, vì rằng tên key đã có trước đó:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"}                           |
+------------------------------------------------------+

Việc normalization được thực hiện bởi MySQL cũng sắp xếp các key của một đối tượng JSON (phục vụ mục đích làm cho tra cứu hiệu quả hơn). Kết quả của việc sắp xếp này có thể thay đổi và không đảm bảo được ổn định qua các phiên bản. Ngoài ra, khoảng trắng thêm giữa các key, value, hoặc các element trong văn bản gốc được loại bỏ.

Function MySQL sinh giá trị JSON luôn luôn trả về giá trị normalized. Trong bối cảnh kết hợp nhiều mảng, mảng được sáp nhập vào một mảng duy nhất bằng cách kết hợp các mảng được đặt tên sau đó đến cuối mảng đầu tiên. Trong ví dụ sau đây, JSON_MERGE()kết hợp các tham số của mình vào một mảng duy nhất:

mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');
+-----------------------------------------------------+
| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |
+-----------------------------------------------------+
| [1, 2, "a", "b", true, false]                       |
+-----------------------------------------------------+

Khi sáp nhập nhiều đối tượng tạo ra một đối tượng duy nhất. Nếu có nhiều đối tượng có cùng key, giá trị cho key đó trong đối tượng kết quả là một mảng chứa các giá trị:

mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3}                      |
+----------------------------------------------------+

Giá trị Nonarray sử dụng trong bối cảnh đòi hỏi một mảng giá trị được autowrapped: Giá trị được bao bởi ký tự [ và ] để chuyển đổi nó vào một mảng. Trong statement sau đây, mỗi đối số được autowrapped như là một mảng ([1], [2]). Sau đó sáp nhập để tạo ra một mảng kết quả duy nhất:

mysql> SELECT JSON_MERGE('1', '2');
+----------------------+
| JSON_MERGE('1', '2') |
+----------------------+
| [1, 2]               |
+----------------------+

Các giá trị mảng và đối tượng này được sáp nhập bởi autowrapping các đối tượng như là một mảng và sáp nhập hai mảng:

mysql> SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');
+------------------------------------------------+
| JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') |
+------------------------------------------------+
| [10, 20, {"a": "x", "b": "y"}]                 |
+------------------------------------------------+

Searching and Modifying JSON Values

Một biểu thức đường dẫn JSON chọn ra một giá trị trong một tài liệu JSON. Biểu thức đường dẫn hữu ích với function trích xuất các bộ phận hoặc chỉnh sửa tài liệu JSON, để chỉ định nơi trong tài liệu đó để hoạt động. Ví dụ, truy vấn sau đây trích từ một tài liệu JSON các giá trị của các thành phần với key là name:

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

Cú pháp đường dẫn sử dụng ký tự $ đại diện cho các tài liệu JSON được xem xét, tùy chọn tiếp theo bởi selectors chỉ các phần tiếp cụ thể hơn của tài liệu:

• Phần tiếp theo là một tên tên key các thành phần trong một đối tượng với các key nhận được. Tên key phải được quy định trong dấu ngoặc kép nếu tên không dấu là không hợp lệ trong các biểu thức đường dẫn (ví dụ, nếu nó có chứa một dấu cách).
• [N]nối vào một path sẽ lựa chọn một tên mảng giá trị tại vị trí N trong mảng. Vị trí mảng là các số nguyên bắt đầu từ 0.
• Path có thể chứa các kí hiệu *hoặc **:
  • .[*] biểu thị cho giá trị của tất cả các thành phần trong một đối tượng JSON.
  • [*] biểu thị cho giá trị của tất cả các phần tử trong một mảng JSON.
  • prefix**suffix biểu thị cho tất cả các đường dẫn bắt đầu với tiền tố prefix và kết thúc với hậu tố suffix.
• Một đường dẫn không tồn tại trong tài liệu biểu thị cho NULL.

Bây giờ $$đại diện mảng JSON này với ba phần tử: [3, { "a": [5, 6], "b": 10}, [99, 100]]

Khi đó:

• $$0]biểu thị cho 3.
• $$0]biểu thị cho 3.
• $$1]biểu thị cho {"a": [5, 6], "b": 10}.
• $$2]biểu thị cho [99, 100].
• $$3]biểu thị cho NULL (nó dùng để chỉ các phần tử mảng thứ tư, không tồn tại).

Vì [1] và [2] biểu thị cho các giá trị nonscalar, chúng có thể được sử dụng làm cơ sở cho các đường dẫn biểu thức cụ thể hơn để chọn các giá trị lồng nhau. Ví dụ:

• $$1].a biểu thị cho [5, 6].
• $$1].a[1]biểu thị cho 6.
• $$1].b biểu thị cho 10.
• $$2][0] biểu thị cho 99.

Như đã đề cập trước đó, các thành phần đường dẫn là tên các key phải được đặt trong dấu nháy kép nếu tên key là không hợp pháp trong biểu thức đường dẫn. Bây giờ $$đại diện giá trị này: {"a fish": "shark", "a bird": "sparrow"}. Các key đều chứa một dấu cách và phải được trích dẫn:

• $$"a fish" biểu thị cho shark.
• $$"a bird" biểu thị cho sparrow.

Đường dẫn sử dụng các kí hiệu biểu thị cho một mảng có thể chứa nhiều giá trị:

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]]                                       |
+---------------------------------------------------------+

Trong ví dụ sau, đường dẫn $**.b để biểu thị cho nhiều đường dẫn ($.a.bvà $.c.b) và tạo ra một mảng các giá trị phù hợp với đường dẫn:

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+

Trong MySQL 5.7.9 trở lên, bạn có thể sử dụng column->path với một cột JSON định danh và biểu thức đường dẫn JSON như là một từ đồng nghĩa với JSON_EXTRACT(column, path).

Một số function cần một tài liệu JSON tồn tại, sửa đổi nó bằng một cách nào đó, và trả lại kết quả là tài liệu đã sửa đổi. Biểu thức đường dẫn chỉ ra nơi trong văn bản để thực hiện thay đổi. Ví dụ, function JSON_SET(), JSON_INSERT(), và JSON_REPLACE() cần một tài liệu JSON, cộng với một hoặc nhiều cặp path/value mô tả nơi để sửa đổi tài liệu và các giá trị sử dụng. Các function khác nhau trong cách xử lý các giá trị tồn tại và không tồn tại trong tài liệu. Hãy xem xét tài liệu này:

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';

JSON_SET() thay thế các giá trị cho các đường tồn tại và bổ sung thêm giá trị cho các đường dẫn không tồn tại :.

mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+

Trong trường hợp này, đường dẫn [1].b[0]lựa chọn một giá trị tồn tại (true), rồi được thay thế bằng giá trị sau đường dẫn (1). Đường dẫn `[2][2]không tồn tại, vì vậy giá trị tương ứng (2) sẽ được thêm vào giá trị chọn bằng$$2]`.

JSON_INSERT() thêm giá trị mới nhưng không thay thế các giá trị hiện tại:

mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+

JSON_REPLACE() thay thế giá trị hiện có và bỏ qua các giá trị mới:

mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+.

JSON_REMOVE()nhận một tài liệu JSON và một hoặc nhiều path xác định giá trị cần được loại bỏ từ tài liệu. Giá trị trả về là tài liệu gốc trừ đi giá trị của đường dẫn tồn tại trong tài liệu được lựa chọn:

mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+