Dữ liệu JSON trong MySQL

Bắt đầu từ Mysql 5.7.8, Mysql hỗ trợ kiểu dữ liệu JSON được xác định nghĩa bởi RFC 7159 , cho phép truy cập vào dữ liệu trong các tài liệu JSON (Javascript Notation Object).
Các điểm lợi của việc lưu dữ liệu vào cột dạng JSON:

• Tự động xác thực các dữ liệu dạng JSON
• Tối ưu hoá định dạng lưu trữ
• Các hàm trong SQL hỗ trợ việc thao tác với dữ liệu cột JSON như tạo mới, quản lý hay tìm kiếm
• Chuẩn hoá, hợp nhất dữ liệu JSON.

Tạo Dữ Liệu JSON

Dữ liệu dạng JSON có 2 dạng phổ biến là Mảng JSON(Array Json) và Đối tượng JSON(Object Json). Mảng Json chứa các giá trị được phân tách với nhau bởi dấu , và được đặt bên trong [ và ] :

["abc", 10, null, true, false]
Đối tượng Json chứa tập hợp các cặp khoá – giá trị (key – value) được phân tách với nhau cũng bởi dấu , và được đặt bên trong { và }:
{"k1": "value", "k2": 10}
Tuy nhiên với nhiều trường hợp phức tạp, dữ liệu JSON còn có dạng lồng nhau (Nesting):
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}
trong mảng JSON chứ đối tượng JSON và ngược lại.
Thay vì tự đặt ra đối tượng JSON, bạn cũng có thể sử dụng JSON_OBJECT chức năng tích hợp.
Các JSON_OBJECT chức năng chấp nhận một danh sách các cặp khóa / giá trị theo hình thức JSON_OBJECT(key1, value1, key2, value2, … key(n), value(n)) và trả về một đối tượng JSON.
/* Let's sell some mobilephones */

INSERT INTO `e_store`.`products`( `name` , `brand_id` , `category_id` , `attributes` )

VALUES( 'Desire' , '2' , '2' ,

JSON_OBJECT( "network" , JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,

"body" ,

"5.11 x 2.59 x 0.46 inches" ,

"weight" ,

"143 grams" ,

"sim" ,

"Micro-SIM" ) );


Một chức năng khác mà chúng ta có thể sử dụng để tạo các đối tượng JSON là JSON_MERGE hàm.
Các JSON_MERGE chức năng có nhiều đối tượng JSON và tạo ra một, tổng hợp đối tượng duy nhất.
INSERT INTO `e_store`.`products`( `name` , `brand_id` , `category_id` , `attributes` )

VALUES( 'Explorer' , '3' , '3' ,

JSON_MERGE(

'{"sensor_type": "CMOS"}' ,

'{"processor": "Digic DV III"}' ,

'{"scanning_system": "progressive"}' ,

'{"mount_type": "PL"}' ,

'{"monitor_type": "LCD"}'

) );
Thao Tác với Dữ Liệu Cột JSON

Hãy tưởng tượng khi bạn muốn lấy một giá trị nào đó trong một cột dữ liệu JSON(ở đây lấy giá trị name):

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

Ở đây Mysql cung cấp hàm JSON_EXTRACT() giúp trả về dữ liệu từ một tài liệu JSON. Dữ liệu được trả về dựa theo đường dẫn cung cấp, trong ví dụ trên chúng ta thấy đường dẫn ở đây là $.name.

Tìm hiểu sâu hơn về đường dẫn này nhé: $ là kí tự thể hiện tài liệu JSON đang được xét đến , name chính là tên khoá ta muốn lấy. Tuy nhiên với các dạng dữ liệu JSON thì đường dẫn lại khác nhau.

Với dạng Mảng JSON thì đường dẫn có dạng $[n] còn với Đối tượng JSON là $.key, n là vị trí giá trị chúng ta muốn lấy trong mảng và key là tên khoá của giá trị chúng ta muốn lấy.
 SELECT JSON_EXTRACT('["abc", 14, "Aztalan"]', '$[0]');
+---------------------------------------------------------+
| JSON_EXTRACT('["abc", 14, "Aztalan"]', '$[0]')          |
+---------------------------------------------------------+
| "abc"                                                   |
+---------------------------------------------------------+

Tuy nhiên với trường hợp dữ liệu dạng lồng nhau thì đường dẫn của chúng ta không đơn giản như vậy. Cùng xem ví dụ sau nhé:
[3, {"a": [5, 6], "b": 10}, [99, 100]]
Đây là một mảng JSON vậy nếu chúng ta muốn lấy giá trị 3 trong mảng thì đường dẫn sẽ là $[0] (trong một array vị trí đầu tiên sẽ bắt đầu là 0), nhưng nếu chọn lấy giá trị thứ 2 ta sẽ có một đối tượng JSON {“a”: [5, 6], “b”: 10} vậy nếu bài toán đặt ra là muốn lấy giá trị của khoá b trong đối tượng JSON này thì ta phải có đường dẫn như nào?. Để lấy giá trị của khoá b ta sẽ có đường dẫn là $[1].b. Với tuỳ trường hợp dạng dữ liệu JSON ta sẽ có đường dẫn là $[n].key với dạng mảng lồng object JSON và $.key[n] mới dạng object lồng mảng JSON.

Một số hàm phổ biến được Mysql hỗ trợ như JSON_SET, JSON_INSERT, JSON_REPLACE hay JSON_REMOVE.

 SET @j = '["a", {"b": [true, false]}, [10, 20]]';
JSON_SET () thay thế các giá trị cho các đường dẫn tồn tại và thêm các giá trị cho các đường dẫn không tồn tại:
 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]]      |
+--------------------------------------------+
JSON_INSERT () thêm các giá trị mới nhưng không thay thế các giá trị hiện có:
 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ế các giá trị hiện có và bỏ qua các giá trị mới:
 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 () lấy một tài liệu JSON và một hoặc nhiều đường dẫn chỉ định các giá trị sẽ bị xóa khỏi tài liệu. Giá trị trả về là tài liệu gốc trừ đi các giá trị được chọn bởi các đường dẫn tồn tại trong tài liệu:
SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

Bài viết khác
Hàm bỏ dấu tiếng việt trong php
Elementor công cụ builder website wordpress 2019
WordPress nâng cao 1: Custom post type
Dùng hàm preg_match lấy html từ website khác
Cách tạo meta box trong custom post wordpress
Xóa slug trên custom post và custom taxonomy: Phần 1
Bình luận
 Bình luận