Bạn có thể lưu trữ đối tượng JSON trong MySQL không?

MySQL hỗ trợ kiểu dữ liệu JSON gốc hỗ trợ xác thực tự động và tối ưu hóa lưu trữ và truy cập các tài liệu JSON. Mặc dù dữ liệu JSON tốt nhất nên được lưu trữ trong cơ sở dữ liệu NoSQL như MongoDB, đôi khi bạn vẫn có thể gặp các bảng có dữ liệu JSON. Trong phần đầu tiên của bài đăng này, chúng tôi sẽ giới thiệu cách trích xuất dữ liệu từ trường JSON trong MySQL bằng các câu lệnh đơn giản. Và trong phần thứ hai, chúng tôi sẽ giới thiệu cách tổng hợp dữ liệu từ các bảng MySQL thành các mảng hoặc đối tượng JSON mà sau đó có thể được sử dụng thuận tiện trong các ứng dụng của bạn

Hình ảnh từ Pixabay

Hệ thống được thiết lập tương tự như hệ thống đã giới thiệu trong bài viết trước về cách thực thi truy vấn SQL trong Python. Nếu bạn đã thiết lập hệ thống theo hướng dẫn trong bài viết đó, bạn có thể chuyển sang phần tiếp theo. Nếu không, bạn có thể làm theo các hướng dẫn đơn giản bên dưới để thiết lập hệ thống của mình. Để biết giải thích chi tiết về các lệnh và tùy chọn, vui lòng tham khảo bài viết trước

Về cơ bản, chúng tôi sẽ khởi động một máy chủ MySQL cục bộ trong bộ chứa Docker

Bạn có thể thực hiện trực tiếp các truy vấn SQL trong bảng điều khiển bắt đầu ở trên. Ngoài ra, nếu bạn thích sử dụng giao diện đồ họa, bạn có thể cài đặt và sử dụng DBeaver, một trình quản lý cơ sở dữ liệu đồ họa tuyệt vời cho tất cả các loại cơ sở dữ liệu. Nó thực sự đáng để thử nếu bạn đang vật lộn với MySQL workbench. Để biết thêm chi tiết về cách cài đặt và thiết lập DBeaver, bài viết này có một bản tóm tắt ngắn nhưng hữu ích

Trước tiên, hãy khám phá các hàm và toán tử phổ biến của MySQL có thể được sử dụng để trích xuất dữ liệu từ trường JSON

Có hai loại giá trị JSON chính trong MySQL

• Mảng JSON — Danh sách các giá trị được phân tách bằng dấu phẩy và được đặt trong dấu ngoặc vuông ([])
• Đối tượng JSON — Một từ điển/bản đồ băm/đối tượng (tên khác nhau trong các ngôn ngữ lập trình khác nhau) với một tập hợp các cặp khóa-giá trị được phân tách bằng dấu phẩy và được đặt trong dấu ngoặc nhọn ({})

Các mảng và đối tượng JSON có thể được lồng vào nhau, như chúng ta sẽ thấy sau

Chúng ta có thể sử dụng hàm JSON_EXTRACT để trích xuất dữ liệu từ trường JSON. Cú pháp cơ bản là

JSON_EXTRACT(json_doc, path)

Đối với một mảng JSON, đường dẫn được chỉ định bằng $[index], trong đó chỉ mục bắt đầu từ 0

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

Đối với đối tượng JSON, đường dẫn được chỉ định bằng $.key, trong đó key là khóa của đối tượng

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

Nếu chỉ có hai đối số cho JSON_EXTRACT như được sử dụng ở trên, chúng ta có thể sử dụng toán tử -> là bí danh của JSON_EXTRACT. Để minh họa cách sử dụng toán tử này, chúng ta cần có một bảng có trường JSON. Vui lòng sao chép các truy vấn SQL sau và thực hiện chúng trong bảng điều khiển MySQL hoặc DBeaver

Đặc biệt, MySQL xử lý các chuỗi được sử dụng trong ngữ cảnh JSON bằng cách sử dụng bộ ký tự

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

0 và đối chiếu

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

1. Bộ ký tự là một tập hợp các ký hiệu và mã hóa, còn đối chiếu là một bộ quy tắc để so sánh các ký tự trong một bộ ký tự. Tốt hơn là tạo bảng có trường JSON bằng cách sử dụng bộ ký tự và đối chiếu tương ứng

Bởi vì

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

1 là đối chiếu nhị phân, các khóa phân biệt chữ hoa chữ thường và chúng tôi cần chỉ định chúng bằng chữ hoa chữ thường chính xác

Bây giờ chúng ta có thể sử dụng toán tử -> để trích xuất dữ liệu từ trường JSON

Như chúng ta thấy, -> chỉ là cách gọi tắt hoặc bí danh của JSON_EXTRACT

Thật thú vị, các trích dẫn vẫn còn đó cho

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

6 và

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

7. Đây không phải là những gì chúng tôi muốn. Chúng tôi muốn xóa dấu ngoặc kép, tương tự như trường

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

8

Để loại bỏ các trích dẫn của giá trị được trích xuất, chúng ta cần sử dụng hàm

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

9. Vì

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

0 được sử dụng phổ biến nên cũng có một toán tử viết tắt cho tổ hợp này, đó là

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

1. Hãy xem nó trong thực tế

Nó chứng minh rằng

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

1 và

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

3 có cùng kết quả. Vì

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

1 ít phải gõ hơn nên nó được ưu tiên trong hầu hết các trường hợp

Tuy nhiên, nếu bạn muốn trích xuất dữ liệu từ một mảng JSON lồng nhau hoặc đối tượng JSON, bạn không thể sử dụng chuỗi -> hoặc

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

1. Bạn chỉ có thể sử dụng -> và

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

1 cho cấp cao nhất và cần sử dụng JSON_EXTRACT cho các cấp lồng nhau. Hãy trích xuất điểm số cho mỗi học sinh

Chúc mừng. Nó hoạt động như mong đợi

Điểm mấu chốt để trích xuất dữ liệu từ trường JSON trong MySQL

• Sử dụng $.key để trích xuất giá trị của khóa từ đối tượng JSON
• Sử dụng $[index] để trích xuất giá trị của phần tử từ mảng JSON
• Sử dụng -> làm lối tắt cho JSON_EXTRACT nếu giá trị không phải là chuỗi
• Sử dụng

  mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
  +-------------------------------------------------------+
  | JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
  +-------------------------------------------------------+
  | "John"                                                |
  +-------------------------------------------------------+

  1 làm lối tắt cho

  mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
  +-------------------------------------------------------+
  | JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
  +-------------------------------------------------------+
  | "John"                                                |
  +-------------------------------------------------------+

  3 nếu giá trị là một chuỗi và bạn muốn xóa dấu ngoặc kép cho chuỗi được trích xuất
• Nếu bạn muốn trích xuất dữ liệu từ một mảng JSON lồng nhau hoặc đối tượng JSON, bạn không thể sử dụng chuỗi -> hoặc

  mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
  +-------------------------------------------------------+
  | JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
  +-------------------------------------------------------+
  | "John"                                                |
  +-------------------------------------------------------+

  1. Bạn chỉ có thể sử dụng -> và

  mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
  +-------------------------------------------------------+
  | JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
  +-------------------------------------------------------+
  | "John"                                                |
  +-------------------------------------------------------+

  1 cho cấp cao nhất và cần sử dụng JSON_EXTRACT cho các cấp lồng nhau

Có khá nhiều chức năng khác để làm việc với dữ liệu JSON trong MySQL. Tuy nhiên, nếu bạn cần sử dụng các hàm này để xác thực/tìm kiếm trường JSON của mình hoặc thực hiện các thao tác CRUD trên đó, bạn nên cân nhắc nghiêm túc việc sử dụng MongoDB để lưu trữ trường JSON. MongoDB chuyên nghiệp và thuận tiện hơn nhiều trong việc xử lý dữ liệu phi cấu trúc (tài liệu)

Trên đây chúng tôi đã giới thiệu cách trích xuất giá trị từ trường JSON trong MySQL. Bây giờ chúng ta sẽ tìm hiểu ngược lại và khám phá cách chọn dữ liệu JSON từ bảng MySQL. Để tiếp tục với phần này, chúng tôi cần một số dữ liệu giả để chơi với. Vui lòng sao chép các truy vấn SQL sau và chạy chúng trong bảng điều khiển MySQL hoặc DBeaver

Đối với bảng này, ký tự mặc định và đối chiếu được sử dụng. Với hai truy vấn này, chúng ta tạo một bảng lưu trữ dữ liệu được trích xuất từ ​​​​phần đầu tiên. Đây là một nhiệm vụ phổ biến đối với đường ống và phân tích dữ liệu, cụ thể là thực hiện một số phân tích dữ liệu sau khi làm sạch dữ liệu. Trong thực tế, bạn sẽ muốn lưu trữ điểm trong một bảng riêng biệt để các bảng được chuẩn hóa hơn. Tuy nhiên, ở đây dữ liệu được đặt trong cùng một bảng để đơn giản hóa việc trình diễn

Bây giờ chúng ta có thể tổng hợp dữ liệu thành một mảng JSON bằng hàm $[index]1

Chúng ta cũng có thể tổng hợp dữ liệu thành một đối tượng JSON bằng cách sử dụng hàm $[index]2

Dữ liệu tổng hợp sau đó có thể được sử dụng trực tiếp trong ứng dụng của bạn. $[index]1 và $[index]2 có thể giúp bạn tiết kiệm công sức tổng hợp dữ liệu trong ứng dụng của mình và đôi khi có thể hữu ích. Ví dụ: sau đó, bạn có thể sử dụng phương thức $[index]5 để chuyển đổi chuỗi JSON thành một mảng hoặc từ điển trong Python

Nếu bạn cần thực hiện các truy vấn SQL đơn giản của $[index]1 và $[index]2 bằng Python, bạn có thể sử dụng gói SQLAlchemy như được minh họa trong bài đăng này

Trong bài viết này, chúng tôi đã giới thiệu cách làm việc với dữ liệu JSON trong MySQL. Trong phần đầu tiên, các hàm và toán tử được sử dụng để trích xuất dữ liệu từ trường JSON được thảo luận với các ví dụ đơn giản. Và trong phần thứ hai, chúng tôi đã làm ngược lại và tổng hợp dữ liệu đã chuẩn hóa thành các mảng hoặc đối tượng JSON mà sau đó có thể được sử dụng trực tiếp trong chương trình của bạn. Thông thường chúng ta nên tránh lưu trữ dữ liệu phi cấu trúc (tài liệu) trong MySQL. Tuy nhiên, nếu không thể tránh khỏi, kiến ​​thức trong bài viết này sẽ giúp ích cho công việc của bạn

Chúng ta có nên lưu trữ JSON trong MySQL không?

Bạn có thể lưu trữ các đối tượng JSON trong SQL không?

Làm cách nào để lưu trữ đối tượng JSON trong cơ sở dữ liệu?

Tôi có thể nhập JSON vào MySQL không?