Mysql json_extract xóa dấu ngoặc

SQL Server 2016 đi kèm với nhiều tùy chọn JSON. Chúng tôi có một yêu cầu như chọn dữ liệu bảng thành định dạng JSON và cũng kết hợp kết quả này với JSON của bảng khác

Khi bạn đang cố lấy dữ liệu có định dạng JSON bằng FOR JSON PATH, nó sẽ thêm [dấu ngoặc vuông] vào chuỗi JSON, điều này có thể gây ra sự cố khi bạn đang kết hợp nhiều JSON

Bạn có thể sử dụng tùy chọn KHÔNG_ARRAY_WRAPPER để xóa [dấu ngoặc vuông] bổ sung này

Dưới đây là một minh chứng đầy đủ về điều này

Tạo một bảng với các bản ghi mẫu

1

2

3

4

5

6

7

8

9

10

11

12

13

14

TẠO BẢNG tbl_Students

[

KHÓA CHÍNH Rno INT

,STudName VARCHAR[20]

, Tên lớp CHAR[1]

]

ĐI

CHÈN VÀO tbl_Students

GIÁ TRỊ

[1,'Anvesh','A'],[2,'Neevan','B'],[3,'Toby','C']

,[5,'Roy','A'],[4,'Jenny','B'],[6,'Kaviy','C']

,[7,'Martin','A'],[8,'Laxmi','B'],[9,'Nion','C']

ĐI

 

Chuẩn bị JSON bằng FOR JSON PATH.
Bạn có thể thấy thêm [ ] trong chuỗi kết quả.

1

2

3

4

5

6

7

8

CHỌN HÀNG ĐẦU 2 *

TỪ tbl_Students

ĐẶT HÀNG BỞI Rno

ĐỐI VỚI CON ĐƯỜNG JSON

ĐI

 

--Kết quả

--[{"Không". 1,"STudName". "Anvesh","Tên Lớp". "A"},{"Rno". 2,"STudName". "Neevan","Tên Lớp". "B"}]

Chuẩn bị JSON bằng cách sử dụng FOR JSON PATH, WITHOUT_ARRAY_WRAPPER.
Bây giờ, bạn có thể tìm chuỗi kết quả mà không cần [ ].

1

2

3

4

5

6

7

8

CHỌN HÀNG ĐẦU 2 *

TỪ tbl_Students

ĐẶT HÀNG BỞI Rno

ĐỐI VỚI CON ĐƯỜNG JSON, KHÔNG CÓ_ARRAY_WRAPPER

ĐI

 

--Kết quả

--{"Không". 1,"STudName". "Anvesh","Tên Lớp". "A"},{"Rno". 2,"STudName". "Neevan","Tên Lớp". "B"}

20 tháng 4 năm 2017 Anvesh Patel

Mời bạn tham khảo các bài viết liên quan khác

• Máy chủ SQL 2016. Sử dụng STRING_ESCAPE để thoát khỏi dấu ngoặc đơn, dấu ngoặc kép, dấu gạch chéo về phía trước
• Máy chủ SQL 2016. Tạo dữ liệu được định dạng JSON từ Dữ liệu bảng
• Máy chủ SQL 2016. Sử dụng OPENJSON[] để trích xuất dữ liệu JSON từ Tham số đầu vào
• Máy chủ SQL 2016. Định cấu hình TempDB cho tệp Tự động phát triển và Nhiều tệp dữ liệu
• Máy chủ SQL 2016. STRING_SPLIT để tách một chuỗi theo các dấu phân cách khác nhau
• Máy chủ SQL 2016. Hỗ trợ SSMS Chỉnh sửa 200 hàng TOP để xem
• Máy chủ SQL 2016. Đã giới thiệu biểu thức AT TIME ZONE để chọn các Múi giờ khác SQL Server 2016. Wide World Importers [WWI] - Cơ sở dữ liệu mẫu mới

Nếu bạn muốn tải xuống phiên bản PDF của hướng dẫn này, hãy nhập email của bạn vào bên dưới và bạn sẽ nhận được nó ngay

Mục lục

• JSON là gì và tại sao tôi nên sử dụng nó?
• Cách tạo và điền trường JSON trong MySQL
• Cách đọc và lọc dữ liệu JSON trong MySQL
• Cách cập nhật dữ liệu JSON trong MySQL
• Cách xóa khỏi trường JSON trong MySQL
• Xác thực dữ liệu JSON
• Cải thiện hiệu suất của các truy vấn JSON
• Mẹo để làm việc với JSON trong MySQL
• Sự kết luận

JSON là gì và tại sao tôi nên sử dụng nó?

JSON là viết tắt của JavaScript Object Notation, và đó là một cách để định dạng và lưu trữ dữ liệu

Dữ liệu có thể được biểu diễn ở định dạng JSON để các ứng dụng khác hoặc các phần của ứng dụng có thể đọc và hiểu dữ liệu đó

Nó tương tự như HTML hoặc XML – nó thể hiện dữ liệu của bạn ở một định dạng nhất định mà mọi người có thể đọc được nhưng được thiết kế để các ứng dụng có thể đọc được

 

Tại sao nên sử dụng JSON trong cơ sở dữ liệu của bạn?

Vậy tại sao bạn lại sử dụng dữ liệu JSON trong cơ sở dữ liệu của mình?

Nếu bạn cần một cấu trúc linh hoạt

Cấu trúc cơ sở dữ liệu được chuẩn hóa, một cấu trúc có bảng, cột và mối quan hệ, hoạt động tốt trong hầu hết các trường hợp. Những cải tiến gần đây trong thực tiễn phát triển cũng có nghĩa là việc thay đổi bảng không còn quan trọng như trước đây, do đó, có thể điều chỉnh cơ sở dữ liệu của bạn sau khi nó được đưa vào sản xuất

Tuy nhiên, nếu yêu cầu của bạn có nghĩa là cấu trúc dữ liệu của bạn cần linh hoạt, thì trường JSON có thể phù hợp với cơ sở dữ liệu của bạn

Một ví dụ có thể là nơi người dùng có thể thêm các thuộc tính tùy chỉnh. Nếu nó được thực hiện bằng cách sử dụng cơ sở dữ liệu chuẩn hóa, điều này có thể liên quan đến việc thay đổi bảng hoặc tạo thiết kế Giá trị thuộc tính thực thể hoặc một số phương pháp khác

Nếu một trường JSON được sử dụng cho việc này, thì việc thêm và duy trì các thuộc tính tùy chỉnh này sẽ dễ dàng hơn nhiều

Dữ liệu JSON cũng có thể được lưu trữ trong cơ sở dữ liệu của bạn và được xử lý bởi ORM [Trình ánh xạ quan hệ đối tượng] hoặc mã ứng dụng của bạn, vì vậy cơ sở dữ liệu của bạn có thể không cần thực hiện thêm bất kỳ công việc nào

 

Dữ liệu JSON trông như thế nào?

Đây là một ví dụ đơn giản về dữ liệu JSON

{
  "id": "1",
  "username": "jsmith",
  "location": "United States"
}

Nó sử dụng kết hợp các dấu ngoặc, dấu hai chấm và dấu ngoặc kép khác nhau để thể hiện dữ liệu của bạn

Hãy cùng xem thêm một số ví dụ

 

Tên/Cặp giá trị

Dữ liệu JSON được viết dưới dạng cặp tên/giá trị. Một cặp tên/giá trị là hai giá trị được đặt trong dấu ngoặc kép

Đây là một ví dụ về cặp tên/giá trị

"username": "jsmith"

Tên là “tên người dùng” và giá trị là “jsmith”. Chúng được ngăn cách bởi dấu hai chấm “. ”

Điều này có nghĩa là đối với thuộc tính của tên người dùng, giá trị là jsmith. Tên trong JSON cần được đặt trong dấu ngoặc kép

 

Các đối tượng

Dữ liệu JSON có thể được đặt trong dấu ngoặc nhọn cho biết đó là một đối tượng

{"username": "jsmith"}

Đây là dữ liệu giống như ví dụ trước, nhưng bây giờ nó là một đối tượng. Điều này có nghĩa là nó có thể được coi là một đơn vị bởi các khu vực khác của ứng dụng

Điều này giúp ích như thế nào?

{
  "username": "jsmith",
  "location": "United States"
}

Có thể thêm các thuộc tính bổ sung hoặc cặp tên/giá trị bằng cách sử dụng dấu phẩy để phân tách chúng

Bạn cũng sẽ nhận thấy trong ví dụ này, các dấu ngoặc nhọn nằm trên các dòng riêng của chúng và dữ liệu được thụt vào. Đây là tùy chọn. nó chỉ được thực hiện để làm cho nó dễ đọc hơn

 

Mảng

JSON cũng hỗ trợ mảng, là tập hợp các bản ghi trong một đối tượng. Mảng trong JSON được đặt trong dấu ngoặc vuông và có tên

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

Trong ví dụ này, đối tượng này có một thuộc tính gọi là “posts”. Giá trị của các bài đăng là một mảng mà chúng ta có thể thấy bằng dấu ngoặc vuông mở “[“

Bên trong dấu ngoặc vuông, chúng tôi có một tập hợp các dấu ngoặc nhọn, biểu thị một đối tượng và bên trong chúng tôi có id là 1 và tiêu đề Chào mừng. Chúng tôi có một bộ dấu ngoặc nhọn khác cho biết một đối tượng khác

Hai đối tượng này là bài viết và chúng được chứa trong một mảng

Và điều đó bao gồm những điều cơ bản về JSON là gì

Nếu JSON là mới đối với bạn, đừng lo lắng, nó sẽ dễ dàng hơn khi bạn làm việc với nó nhiều hơn

Nếu bạn đã có kinh nghiệm với JSON, bạn sẽ thấy phần còn lại của hướng dẫn hữu ích hơn khi chúng tôi đi vào chi tiết cách làm việc với JSON trong MySQL

 

Cách tạo và điền trường JSON trong MySQL

Vậy là bạn đã tìm hiểu một chút về dữ liệu JSON và lý do bạn có thể muốn sử dụng nó

Làm cách nào để chúng tôi tạo một trường trong MySQL?

Tạo trường JSON

Chúng tôi tạo một trường mới với kiểu dữ liệu là JSON

Đây là một ví dụ

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

Chúng tôi đã tạo một bảng được gọi là sản phẩm. Nó có một id và một tên sản phẩm. Ngoài ra còn có một cột thuộc tính, có kiểu dữ liệu là JSON

Thêm một cột JSON dễ dàng như vậy

Bạn có thể nghĩ, nếu dữ liệu JSON chỉ là dữ liệu văn bản, tại sao chúng ta cần phải làm gì đặc biệt?

Chúng tôi có thể, nhưng lợi ích chính của kiểu dữ liệu JSON là xác thực định dạng. Với một trường CLOB đơn giản, chúng ta có thể lưu trữ một giá trị văn bản. Tuy nhiên, điều đó có nghĩa là chúng ta có thể lưu trữ JSON không hợp lệ. thiếu tên thuộc tính hoặc dấu ngoặc nhọn

Với trường JSON, dữ liệu được xác thực tự động cho chúng tôi. Chúng tôi sẽ không thể lưu trữ dữ liệu không hợp lệ trong bảng

Ngoài ra, chúng tôi có thể sử dụng các hàm MySQL JSON khác nhau trên dữ liệu JSON để làm việc với nó dễ dàng hơn

Thêm dữ liệu vào trường JSON

Bây giờ chúng tôi đã có trường JSON của mình, làm cách nào để thêm dữ liệu vào đó?

Có một số cách. We can enter the data into a JSON-formatted string, or use a built-in MySQL function. Let’s see both of them

We can add our first product like this

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

We can run this statement and the record is inserted. If we select the data from the table, this is what we see

SELECT
id,
product_name,
attributes
FROM product;

idproduct_nameattributes1Chair{“color”. “brown”, “height”. “60cm”, “material”. “wood”}

The JSON data is shown exactly as we entered it

Using the method above, we needed to enter the data in exactly the right format

However, we can use the JSON_OBJECT function to make this easier. The JSON_OBJECT function accepts a list of name/value pairs which are converted to a JSON object

We can insert a record into the table using this function [which has been formatted to make it easier to read]

INSERT INTO product [id, product_name, attributes]
VALUES [2, 'Table', JSON_OBJECT[
  "color", "brown",
  "material", "wood",
  "height", "110cm"
]];

This makes it easier to specify attributes as you don’t need to remember the curly brackets or the colons

We can select from the table to see the results

SELECT
id,
product_name,
attributes
FROM product;

idproduct_nameattributes1Chair{“color”. “brown”, “height”. “60cm”, “material”. “wood”}2Table{“color”. “brown”, “height”. “110cm”, “material”. “wood”}

The JSON data is shown with the data we entered

Inserting Arrays

If you want to insert JSON data that contains arrays, you can either enter it using text in a JSON format, or use a function called JSON_ARRAY

Here’s how to insert an array by just specifying it in a JSON format

"username": "jsmith"

0

This will insert a new product that has an array of drawers. As you can probably see by this statement, reading it [and writing it] is a bit tricky

You can insert simpler arrays using this method too

"username": "jsmith"

1

The INSERT statements will work, and the data will look like this

SELECT
id,
product_name,
attributes
FROM product;

idproduct_nameattributes1Chair{“color”. “brown”, “height”. “60cm”, “chất liệu”. “wood”}2Table{“color”. “brown”, “height”. “110cm”, “material”. “wood”}3Desk{“color”. “black”, “drawers”. [{“side”. “left”, “height”. “30cm”}, {“side”. “left”, “height”. “40cm”}], “material”. “metal”}4Side Table{“color”. “brown”, “material”. [“metal”, “wood”]}

There is an easier way to insert array data in JSON in MySQL

Inserting Arrays with JSON_ARRAY

Hàm JSON_ARRAY trong MySQL cho phép bạn dễ dàng chỉ định dữ liệu mảng khi chèn dữ liệu JSON vào MySQL

Let’s insert another record into our table. We can use the JSON_ARRAY function along with the JSON_OBJECT function

We’ll insert a simple array, and one with objects

Here’s a simple array

"username": "jsmith"

3

We specify the JSON_ARRAY function, and inside the function, we specify the different attributes to be added to the array

Here’s an insert statement with objects in the array

"username": "jsmith"

4

Using the JSON_ARRAY function helps us to validate the data we’re inserting, and reduces the chances of getting an error due to a misplaced comma, quote, or bracket

Here’s what our table looks like now

SELECT
id,
product_name,
attributes
FROM product;

idproduct_nameattributes1Chair{“color”. “brown”, “height”. “60cm”, “material”. “wood”}2Table{“color”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Desk{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “left”, “height”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}Bàn phụ 4{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ăn{“màu sắc”. “nâu”, “chất liệu”. [“wood”, “metal”]}6Bàn lớn{“color”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Các bản ghi mới với dữ liệu mảng được hiển thị và các mảng được đặt trong dấu ngoặc vuông

Chúng ta có thể thấy hàng cuối cùng, với id là 6, có dấu gạch chéo ngược trong giá trị của nó. Điều này là do thực tế là một đối tượng mảng được sử dụng cho một thuộc tính. Chúng ta sẽ xem cách hiển thị điều này tốt hơn sau

 

Cách đọc và lọc dữ liệu JSON trong MySQL

Khi bạn đã có một số dữ liệu JSON trong bảng, bước tiếp theo là đọc nó

làm sao chúng ta làm việc đó bây giờ?

Chúng ta có thể chạy một câu lệnh SELECT đơn giản để xem dữ liệu trong bảng

SELECT
id,
product_name,
attributes
FROM product;

idproduct_nameattributes1Chair{“color”. “brown”, “height”. “60cm”, “material”. “wood”}2Table{“color”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Desk{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “left”, “height”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}Bàn phụ 4{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ăn{“màu sắc”. “nâu”, “chất liệu”. [“wood”, “metal”]}6Bàn lớn{“color”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Điều này cho chúng ta thấy dữ liệu trong cột JSON và nó trông giống như một giá trị văn bản

Điều tốt với điều này là bất kỳ ứng dụng nào cũng có thể dễ dàng đọc trường này và làm việc với nó theo cách họ muốn [hiển thị, lọc nó, v.v.]

Điều gì sẽ xảy ra nếu chúng tôi muốn làm nhiều hơn trong cơ sở dữ liệu của mình?

Chọn các thuộc tính riêng lẻ

Dữ liệu JSON được lưu trữ trong một thứ trông giống như trường văn bản. Tuy nhiên, khá dễ dàng để lấy các thuộc tính và giá trị ra khỏi trường văn bản này và hiển thị chúng

Chúng tôi có thể trích xuất một giá trị từ trường JSON và hiển thị nó trong một cột riêng biệt. Chúng tôi thực hiện việc này bằng cách sử dụng kết hợp "biểu thức đường dẫn" và hàm JSON_EXTRACT

Chúng ta cần sử dụng hàm JSON_EXTRACT để tìm kiếm một thuộc tính cụ thể trong giá trị JSON. Và chúng ta cần sử dụng một “biểu thức đường dẫn” để chỉ định thuộc tính

Chung ta se lam như thê nao?

Trước tiên, hãy viết biểu thức đường dẫn, sau đó đặt biểu thức đó vào hàm JSON_EXTRACT của chúng ta

Biểu thức đường dẫn cho phép chúng tôi chỉ định thuộc tính mà chúng tôi muốn tìm kiếm. Nó bắt đầu bằng ký hiệu $ và chúng tôi chỉ định một dấu chấm, sau đó là tên của thuộc tính mà chúng tôi đang tìm kiếm

Ví dụ: để chỉ định thuộc tính “màu”, biểu thức đường dẫn ra sẽ như thế này

"username": "jsmith"

7

Để chỉ định thuộc tính “vật liệu”, chúng ta có thể sử dụng biểu thức đường dẫn này

"username": "jsmith"

8

Nếu chúng ta có thuộc tính chiều cao nằm trong thuộc tính kích thước, thì biểu thức đường dẫn của chúng ta sẽ trông như thế này

"username": "jsmith"

9

Chúng tôi sử dụng dấu chấm để chỉ định cấp tiếp theo trong hệ thống phân cấp thuộc tính

Làm thế nào để chúng tôi sử dụng điều này để lọc dữ liệu của chúng tôi?

Hàm JSON_EXTRACT nhận hai tham số

{"username": "jsmith"}

0

Chúng ta có thể sử dụng điều này trong một ví dụ

Hiển thị Trường bằng JSON_EXTRACT

Giả sử chúng tôi muốn hiển thị thuộc tính màu trong một cột riêng trong kết quả của mình

Đầu tiên, chúng ta viết biểu thức đường dẫn cho thuộc tính màu

"username": "jsmith"

7

Sau đó, chúng tôi thêm phần này vào hàm JSON_EXTRACT của chúng tôi

{"username": "jsmith"}

2

Cuối cùng, chúng tôi thêm phần này vào mệnh đề SELECT để hiển thị nó dưới dạng một cột riêng biệt

{"username": "jsmith"}

3

Truy vấn này sẽ hiển thị tất cả các bản ghi và hiển thị thuộc tính màu dưới dạng một cột riêng biệt

idproduct_namecolorattributes1Ghế“nâu”{“màu”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bảng “nâu”{“màu”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Desk“đen”{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}4Bảng phụ“nâu”{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ăn “nâu”{“màu sắc”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}6Bàn lớn“trắng”{“màu”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Chúng ta có thể thấy cột riêng biệt ở đây

Ví dụ về biểu thức đường dẫn

Chúng ta có thể thấy một trường khác sử dụng JSON_EXTRACT bằng cách chỉ định tên thuộc tính

{"username": "jsmith"}

4

Ở đây chúng tôi đang trích xuất thuộc tính có tên là chiều cao. Điều này có sẵn trong một số bản ghi nhưng không có sẵn trong các bản ghi khác

idproduct_nameheightattributes1Ghế“60cm”{“màu sắc”. “brown”, “height”. “60cm”, “material”. “wood”}2Table“110cm”{“color”. “brown”, “height”. “110cm”, “material”. “wood”}3Desknull{“color”. “black”, “drawers”. [{“side”. “left”, “height”. “30cm”}, {“side”. “left”, “height”. “40cm”}], “material”. “metal”}4Side Tablenull{“color”. “brown”, “material”. [“metal”, “wood”]}5Dining Tablenull{“color”. “brown”, “material”. [“wood”, “metal”]}6Large Desknull{“color”. “white”, “drawers”. [“{\”side\”. \”left\”, \”height\”. \”50cm\”}”, “{\”side\”. \”right\”, \”height\”. \”50cm\”}”], “material”. “metal”}

A null value is shown for records that don’t have this attribute

What about attributes that are arrays, such as “material” in this example?

{"username": "jsmith"}

5idproduct_namematerialattributes1Chair“wood”{“color”. “nâu”, “chiều cao”. “60cm”, “material”. “wood”}2Table“wood”{“color”. “brown”, “height”. “110cm”, “material”. “wood”}3Desk“metal”{“color”. “black”, “drawers”. [{“side”. “left”, “height”. “30cm”}, {“side”. “left”, “height”. “40cm”}], “material”. “metal”}4Side Table[“metal”, “wood”]{“color”. “brown”, “material”. [“metal”, “wood”]}5Dining Table[“wood”, “metal”]{“color”. “brown”, “material”. [“wood”, “metal”]}6Large Desk“metal”{“color”. “white”, “drawers”. [“{\”side\”. \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”side\”. \”right\”, \”height\”. \”50cm\”}”], “material”. “metal”}

What if we want to see an attribute that’s inside another attribute? For example, the first of the “drawer” attributes?

Because “drawer” is an array, we can’t use the dot notation to get the attribute like this

{"username": "jsmith"}

6

This will return a null value as there is no attribute called side. it’s part of an array

However, we can use a number to reference the position in the array

Bạn có thể trả lại đối tượng đầu tiên bằng cách sử dụng [0]

{"username": "jsmith"}

7

Có thể tìm thấy đối tượng thứ hai bằng cách sử dụng [1], đối tượng thứ ba bằng cách sử dụng [2], v.v.

So, our query to extract the first item in the array is

{"username": "jsmith"}

8

The results are

idproduct_namedrawerattributes1Chairnull{“color”. “brown”, “height”. “60cm”, “material”. “wood”}2Tablenull{“color”. “brown”, “height”. “110cm”, “chất liệu”. “wood”}3Desk{“side”. “trái”, “chiều cao”. “30cm”}{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}4Side Tablenull]{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ănnull{“màu”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}6Bàn lớn“{\side\”. \”trái\”, \”chiều cao\”. \”50cm\”}”{“màu sắc”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Vì vậy, như bạn có thể thấy, có nhiều cách để bạn có thể sử dụng hàm JSON_EXTRACT với biểu thức đường dẫn để lấy thuộc tính mà bạn muốn

Lọc dữ liệu JSON trong MySQL

Giả sử chúng tôi muốn xem sản phẩm Ghế có màu nâu, chất liệu gỗ và chiều cao 60cm. Nhưng chúng tôi muốn lọc các thuộc tính JSON cho ví dụ này

Hãy thử truy vấn này

{"username": "jsmith"}

9

Chúng ta có thể chạy truy vấn này. Tuy nhiên, chúng tôi không nhận được bất kỳ kết quả

Thật không may, chúng tôi không thể chỉ lọc trên một cột JSON như vậy

Điều gì sẽ xảy ra nếu chúng tôi thử sử dụng từ khóa THÍCH với đối sánh một phần?

{
  "username": "jsmith",
  "location": "United States"
}

0

không có kết quả nào được tìm thấy

Điều đó cũng không mang lại cho chúng tôi kết quả mà chúng tôi muốn. Ngoài ra, sử dụng tìm kiếm ký tự đại diện có thể khá chậm nếu có nhiều dữ liệu trong bảng

Tuy nhiên, có một số tính năng trong MySQL cho phép lọc dữ liệu JSON

Sử dụng JSON_EXTRACT để lọc dữ liệu

Giả sử chúng ta muốn tìm tất cả các sản phẩm có màu nâu. Màu sắc là một phần của cột JSON thuộc tính trong bảng của chúng tôi

Biểu thức đường dẫn của chúng ta sẽ trông như thế này

"username": "jsmith"

7

Chúng ta có thể viết hàm JSON_EXTRACT như thế này

{"username": "jsmith"}

2

Sau đó, chúng tôi thêm phần này vào câu lệnh CHỌN của mình

{
  "username": "jsmith",
  "location": "United States"
}

3

Chúng tôi đã thêm hàm JSON_EXTRACT này vào mệnh đề WHERE của chúng tôi và thêm một điều kiện trong đó màu bằng “nâu”

Kết quả của truy vấn này được hiển thị bên dưới

idproduct_nameattributes1Chair{“color”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bảng{“màu”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}Bàn 4 cạnh{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ăn{“màu sắc”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}

Chúng ta có thể thấy rằng kết quả chỉ hiển thị các bản ghi có thuộc tính màu là màu nâu

Lối tắt cho JSON_EXTRACT

Ví dụ trước của chúng tôi đã sử dụng hàm JSON_EXTRACT để lọc các bản ghi dựa trên các thuộc tính JSON

{
  "username": "jsmith",
  "location": "United States"
}

3

Có một lối tắt trong MySQL cho hàm JSON_EXTRACT. các -> biểu tượng

Điều này có nghĩa là bạn có thể sử dụng -> để viết hàm JSON_EXTRACT. Sử dụng biểu tượng này, cùng một truy vấn có thể được biểu diễn như thế này

{
  "username": "jsmith",
  "location": "United States"
}

5

The results are

idproduct_nameattributes1Chair{“color”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bảng{“màu”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}Bàn 4 cạnh{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ăn{“màu sắc”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}

Điều này cho thấy kết quả tương tự như sử dụng tên chức năng thực tế

Điều này cũng có thể được sử dụng ở bất kỳ nơi nào có thể sử dụng hàm JSON_EXTRACT, chẳng hạn như trong mệnh đề SELECT

{
  "username": "jsmith",
  "location": "United States"
}

6idproduct_namecolorattributes1Ghế màu nâu{“màu sắc”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Nâu bàn{“màu”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Deskblack{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}Bàn 4 cạnh Màu nâu{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Nâu bàn ăn{“màu”. “nâu”, “chất liệu”. [“wood”, “metal”]}6Deskwhite lớn{“color”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Đây là cách bạn có thể thấy một thuộc tính riêng lẻ bằng phím tắt cho JSON_EXTRACT

Tìm kiếm dữ liệu bằng JSON_CONTAINS

Hàm JSON_CONTAINS sẽ tìm kiếm kết quả khớp và trả về 1 nếu tìm thấy hoặc 0 nếu không tìm thấy

Cú pháp là

{
  "username": "jsmith",
  "location": "United States"
}

7

Phải mất ba tham số

• Mục tiêu. tài liệu JSON để tìm kiếm bên trong
• ứng viên. tài liệu JSON để tìm kiếm
• đường dẫn. một giá trị đường dẫn tùy chọn để tìm kiếm trong mục tiêu

Hãy xem một số ví dụ

Ví dụ này xác định xem trường thuộc tính có chứa màu. cặp tên và giá trị màu nâu

{
  "username": "jsmith",
  "location": "United States"
}

8

 

idproduct_namecontain_jsonattributes1Chair1{“color”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bảng1{“màu”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Desk0{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}Bảng phụ 1{“màu sắc”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}Bàn ăn 1{“màu sắc”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}6Bàn lớn0{“màu”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Chúng ta cũng có thể sử dụng JSON_OBJECT để tạo một trong hai tham số, vì JSON_OBJECT trả về kiểu dữ liệu JSON

{
  "username": "jsmith",
  "location": "United States"
}

9idproduct_namecontain_jsonattributes1Chair1{“color”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bảng1{“màu”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Desk0{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}Bảng phụ 1{“màu sắc”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}Bàn ăn 1{“màu sắc”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}6Bàn lớn0{“màu”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Bạn có thể tìm kiếm trong một đường dẫn cụ thể bằng cách sử dụng tham số đường dẫn

Đây là truy vấn để tìm xem các thuộc tính có chứa cặp giá trị tên chiều cao và 40cm trong thuộc tính ngăn kéo hay không

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

0idproduct_namecontain_jsonattributes1Chairnull{“color”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Tablenull{“màu”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Desk1{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}4Side Tablenull{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ănnull{“màu”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}6Bàn lớn0{“màu”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Nó hiển thị 1 cho tìm thấy, 0 cho không tìm thấy và null khi thuộc tính ngăn kéo không tồn tại

Có liên quan. Cách tách một chuỗi trong SQL [một phương pháp để thực hiện việc này là sử dụng các hàm JSON]

Tìm đường dẫn bằng JSON_SEARCH

Hàm JSON_SEARCH cho phép bạn tìm đường dẫn đến một chuỗi đã chỉ định

Cú pháp là

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

1

Các tham số là

• json_doc. đây là trường hoặc tài liệu JSON để tìm kiếm trong
• một_or_all. chỉ định “một” để kết thúc tìm kiếm sau lần khớp đầu tiên hoặc “tất cả” để trả về tất cả các kết quả khớp
• search_str. chuỗi để tìm kiếm trong json_doc
• thoát_char. được sử dụng nếu bạn muốn chỉ định % hoặc _ bằng chữ trong chuỗi tìm kiếm của mình [vì đó là các ký tự đại diện]
• đường dẫn. tìm kiếm chuỗi trong đường dẫn đã chỉ định

Hãy xem một số ví dụ

Dưới đây là một ví dụ về tìm kiếm chuỗi “nâu”

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

2

The results are

idproduct_namesearch_resultattributes1Chair“$. màu”{“màu”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bàn“$. màu”{“màu”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Desknull{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}4Bàn phụ“$. màu”{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ăn“$. màu”{“màu”. “nâu”, “chất liệu”. [“wood”, “metal”]}6Desknull lớn{“color”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Chúng ta có thể thấy rằng một số hàng đã trả về giá trị “$. màu sắc". Đây là đường dẫn chứa từ “nâu”

Điều gì sẽ xảy ra nếu chúng ta tìm kiếm tất cả các đường dẫn có chứa chữ cái “o”

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

3

The results are

idproduct_namesearch_resultattributes1Chair[“$. màu”, “$. chất liệu”]{“màu sắc”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bàn[“$. màu”, “$. chất liệu”]{“màu sắc”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. “gỗ”}3Desknull{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}4Bàn phụ[“$. màu”, “$. chất liệu[1]”]{“màu sắc”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ăn[“$. màu”, “$. chất liệu[0]”]{“màu sắc”. “nâu”, “chất liệu”. [“wood”, “metal”]}6Desknull lớn{“color”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Chúng ta có thể thấy rằng một số hàng có nhiều kết quả, được chứa trong một mảng [dấu ngoặc vuông]

Hàm JSON_SEARCH khá mạnh nếu bạn cần tìm đường dẫn dựa trên chuỗi

Tìm loại giá trị bằng JSON_TYPE

Có một hàm gọi là JSON_TYPE cho phép bạn tìm loại giá trị JSON được cung cấp. Một số ví dụ về các loại là

• Sự vật
• Mảng
• số nguyên

Điều này có thể hữu ích nếu bạn cần thực hiện logic hoặc xử lý nhiều hơn trên một giá trị JSON

Hãy xem một số ví dụ

Đây là một ví dụ về việc sử dụng JSON_TYPE trên một giá trị JSON đầy đủ mà chúng ta đã thấy trong bảng của mình

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

4

The results are

idproduct_namejtypeattributes1ChairOBJECT{“màu sắc”. “đen”, “sâu”. “60cm”, “chiều rộng”. “100cm”, “chiều cao”. “60cm”, “chất liệu”. "gỗ"}

Kết quả của hàm JSON_TYPE cho dữ liệu này là ĐỐI TƯỢNG, cho biết rằng chuỗi JSON là một đối tượng JSON

Chúng ta cũng có thể sử dụng điều này trên các mảng

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

5

Đây là kết quả

json_datajtype[“a”, “b”, “c”]ARRAY

Chúng ta có thể thấy rằng JSON_TYPE đã chỉ ra nó là một mảng

Hàm JSON_TYPE này có thể được sử dụng trên các giá trị khác nhau để xác định loại của nó

Cách cập nhật dữ liệu JSON trong MySQL

Đọc JSON là một chuyện. Nếu bạn cần cập nhật dữ liệu JSON thì sao?

Bạn có thể trích xuất chuỗi, thực hiện một số chuỗi con và công việc thay thế trên chuỗi, đồng thời thêm chuỗi đó vào trường, nhưng điều đó dễ xảy ra lỗi và tốn nhiều công sức

Có một vài chức năng trong MySQL cho phép bạn cập nhật một trường khá dễ dàng

Dưới đây là tóm tắt các phương pháp

Yêu cầuChức năngThêm khóa và giá trị mớiJSON_INSERTCập nhật giá trị cho khóa hiện cóJSON_REPLACEThêm khóa và giá trị mới hoặc cập nhật giá trị của khóa hiện tạiJSON_SET

Hãy xem một số ví dụ về chúng

Chèn một mục mới

Một cách để cập nhật dữ liệu JSON là thêm một mục mới vào giá trị JSON hiện có. Điều này có thể được thực hiện với hàm JSON_INSERT

Cú pháp hàm JSON_INSERT trông như thế này

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

6

Các tham số là

• json_doc. trường JSON sẽ được cập nhật
• đường dẫn. đường dẫn để thêm một giá trị mới cho
• val. giá trị cần thêm cho đường dẫn

Hàm này sẽ trả về giá trị JSON được cập nhật. Vì vậy, bởi vì chúng tôi đang cập nhật một hàng hiện có trong bảng, chúng tôi sử dụng câu lệnh CẬP NHẬT và chức năng này

Đây là mục đầu tiên trong bảng của chúng tôi

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

7idproduct_nameattributes1Chair{“color”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. "gỗ"}

Giả sử chúng ta muốn thêm tên và giá trị thuộc tính mới, ngoài màu sắc, chiều cao và chất liệu đã tồn tại. Thuộc tính mới này sẽ là "chiều rộng" và giá trị là 100cm

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

8

Đường dẫn là “$. width” vì đây là thuộc tính chiều rộng ở cấp cao nhất [không nằm trong thuộc tính khác]

Chúng ta có thể chạy câu lệnh SELECT tương tự như trên để xem giá trị được cập nhật

idproduct_nameattributes1Chair‘{“color”. “nâu”, “chiều rộng”. “100cm”, “chiều cao”. “60cm”, “chất liệu”. "gỗ"}'

Chúng ta có thể thấy rằng "chiều rộng" đã được thêm vào danh sách các thuộc tính

Chèn nơi khóa đã tồn tại

Điều gì xảy ra nếu chúng ta sử dụng hàm JSON_INSERT nhưng khóa đã tồn tại?

• thêm giá trị thứ hai, biến giá trị thuộc tính thành một mảng?
• ghi đè giá trị hiện tại bằng giá trị mới
• thêm tên thuộc tính thứ hai và cặp giá trị
• bị phớt lờ?

Hãy xem nào

Truy vấn này sẽ cập nhật cùng một sản phẩm bằng cách sử dụng khóa thuộc tính đã tồn tại [màu]. Chúng tôi cũng đang thêm một thuộc tính mới gọi là "độ sâu", vì vậy chúng tôi có thể thấy rằng câu lệnh đã chạy thành công [bằng cách xem độ sâu đó đã được thêm vào]

Đây là tuyên bố

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

9

Tuyên bố này chạy thành công

Đây là câu lệnh SELECT và đầu ra

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome"
    },
    {
      "id":"4",
      "title":"What started it all"
     }
  ]
}

7idproduct_nameattributes1Chair{“color”. “nâu”, “sâu”. “60cm”, “chiều rộng”. “100cm”, “chiều cao”. “60cm”, “chất liệu”. "gỗ"}

Thuộc tính độ sâu được thêm vào. Tuy nhiên, thuộc tính màu không thay đổi. Điều này là do hàm JSON_INSERT bỏ qua bất kỳ đường dẫn nào trong hàm mà đường dẫn đã tồn tại. Vì vậy, giá trị màu vẫn là màu nâu và không cập nhật thành màu trắng

Cập nhật giá trị hiện có với JSON_REPLACE

Nếu bạn muốn thay thế giá trị của một thuộc tính bên trong trường JSON bằng một giá trị khác, bạn có thể sử dụng hàm JSON_REPLACE

Cú pháp của MySQL JSON_REPLACE trông như thế này

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

1

Các tham số là

• json_doc. trường JSON sẽ được cập nhật
• đường dẫn. đường dẫn để cập nhật giá trị cho
• val. giá trị để cập nhật

Đường dẫn phải tham chiếu đến khóa thuộc tính hiện có. Nếu nó không tồn tại thì sẽ không có gì được thực hiện [thuộc tính sẽ không được thêm vào]

Hãy xem một số ví dụ

Đây là ID sản phẩm đầu tiên của chúng tôi

idproduct_nameattributes1Chair{“color”. “nâu”, “sâu”. “60cm”, “chiều rộng”. “100cm”, “chiều cao”. “60cm”, “chất liệu”. "gỗ"}

Chúng ta có thể cập nhật giá trị của màu thành màu đen bằng cách sử dụng hàm JSON_REPLACE này. Bởi vì chúng tôi đang cập nhật một giá trị hiện có, chúng tôi sử dụng câu lệnh CẬP NHẬT

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

2

Chúng tôi có thể chọn từ bảng này và xem bản ghi được cập nhật

idproduct_nameattributes1Chair{“color”. “đen”, “sâu”. “60cm”, “chiều rộng”. “100cm”, “chiều cao”. “60cm”, “chất liệu”. "gỗ"}

 

Chèn và cập nhật với JSON_SET

Hàm JSON_INSERT chèn một thuộc tính mới và JSON_REPLACE cập nhật một thuộc tính hiện có

Hàm JSON_SET kết hợp cả hai. cập nhật giá trị nếu nó tồn tại hoặc chèn nó nếu nó không tồn tại

Cú pháp là

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

3

Các tham số là

• json_doc. trường JSON sẽ được cập nhật
• đường dẫn. đường dẫn để cập nhật giá trị cho hoặc chèn một thuộc tính mới cho
• val. giá trị để cập nhật hoặc chèn cho thuộc tính

Lưu ý rằng có dấu ngoặc vuông trong cú pháp, biểu thị các giá trị tùy chọn. Bạn có thể thêm nhiều tham số đường dẫn và giá trị bổ sung để thêm hoặc cập nhật nhiều giá trị

Hãy xem một ví dụ sử dụng ID sản phẩm 2

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

4idproduct_nameattributes2Bảng{“màu sắc”. “nâu”, “chiều cao”. “110cm”, “chất liệu”. "gỗ"}

Hãy thay đổi giá trị màu thành màu đen và độ sâu thành 100cm. Chúng ta có thể sử dụng hàm JSON_SET để làm điều này

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

5

Đây là dữ liệu cập nhật

idproduct_nameattributes2Table{“màu sắc”. “đen”, “sâu”. “100cm”, “chiều cao”. “110cm”, “chất liệu”. "gỗ"}

Chúng ta có thể thấy rằng giá trị màu hiện tại đã được thay đổi thành màu đen và thuộc tính độ sâu mới đã được thêm vào

Bạn cũng có thể xem thêm các ví dụ trong hướng dẫn này tại đây

 

Cách xóa khỏi trường JSON trong MySQL

Có hai thao tác XÓA mà bạn có thể thực hiện khi làm việc với các trường JSON

• xóa một thuộc tính khỏi trường JSON
• xóa một hàng khỏi bảng của bạn

Xóa một hàng bằng JSON_EXTRACT

Việc xóa một hàng khỏi bảng của bạn được thực hiện giống như SQL thông thường. Bạn có thể viết câu lệnh SQL để xóa hàng khớp với ID của mình hoặc sử dụng JSON_EXTRACT

Ví dụ: để xóa tất cả các hàng có thuộc tính màu là màu nâu

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

6

Thao tác này sẽ xóa các bản ghi phù hợp khỏi bảng

Xóa thuộc tính khỏi trường JSON

Một cách khác để xóa dữ liệu JSON là xóa thuộc tính khỏi trường JSON

Điều này khác với việc cập nhật, vì bạn đang loại bỏ hoàn toàn thuộc tính thay vì chỉ cập nhật giá trị của nó sang một thứ khác

Chúng ta có thể làm điều này với câu lệnh UPDATE và hàm JSON_REMOVE

Hàm JSON_REMOVE sẽ xóa dữ liệu khỏi trường JSON. Cú pháp là

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

7

Các tham số là

• json_doc. trường JSON để xóa dữ liệu khỏi
• đường dẫn. đường dẫn đến thuộc tính để loại bỏ

Không cần chỉ định giá trị như các hàm JSON khác

Hãy xem một ví dụ bằng cách xóa một thuộc tính khỏi ID sản phẩm 2

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

4idproduct_nameattributes2Bảng{“màu sắc”. “đen”, “sâu”. “100cm”, “chiều cao”. “110cm”, “chất liệu”. "gỗ"}

Chúng ta có thể chạy câu lệnh CẬP NHẬT với JSON_REMOVE để xóa thuộc tính “chiều cao”

CREATE TABLE product [
  id INT,
  product_name VARCHAR[200],
  attributes JSON
];

9

Sau đó, chúng tôi có thể chọn lại dữ liệu từ bảng để thấy rằng nó đã bị xóa

idproduct_nameattributes2Table{“màu sắc”. “đen”, “sâu”. “100cm”, “chất liệu”. "gỗ"}

Thuộc tính chiều cao không còn trong trường JSON

 

Xác thực dữ liệu JSON

Chúng ta đã thấy trong các ví dụ cho đến nay trong hướng dẫn này rằng kiểu dữ liệu JSON trong MySQL sẽ tự động xác thực dữ liệu cho bạn. Nó đảm bảo bạn chỉ có thể chèn dữ liệu JSON hợp lệ vào trường

MySQL cũng bao gồm một chức năng cho phép bạn kiểm tra xem một chuỗi có phải là trường JSON hợp lệ hay không. Điều này có thể hữu ích nếu bạn đang chấp nhận một chuỗi JSON từ một hệ thống khác

Hàm này là JSON_VALID. Bạn cung cấp cho nó một giá trị và nó trả về 1 nếu đó là chuỗi JSON hợp lệ và 0 nếu không phải

Cú pháp là

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

0

Dưới đây là một số ví dụ

Ví dụ về Chuỗi JSON hợp lệ

Giả sử chúng ta có chuỗi JSON này

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

1

Chúng tôi có thể kiểm tra xem điều này có hợp lệ hay không bằng cách xem nó để biết dấu ngoặc kép và các ký hiệu khác ở đúng vị trí. Hoặc chúng ta có thể chuyển nó vào hàm JSON_VALID

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

2valid_test1

Kết quả là 1 nên đó là một giá trị JSON hợp lệ

Ví dụ về chuỗi JSON không hợp lệ

Nếu giá trị không hợp lệ thì sao?

Chúng ta có thể thấy chuỗi JSON mẫu này

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

3

Thoạt nhìn, nó có vẻ hợp lệ. Hãy sử dụng hàm JSON_VALID để kiểm tra

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

4valid_test0

Kết quả là 0 nên đây không phải là giá trị JSON hợp lệ. Kết quả không cho biết vấn đề nằm ở đâu trong chuỗi được cung cấp, nhưng nó cho bạn biết chuỗi đó không hợp lệ để bạn có thể xem xét kỹ hơn

 

Cải thiện hiệu suất của các truy vấn JSON

Các tính năng và hỗ trợ JSON trong MySQL khá tốt và mỗi phiên bản bao gồm nhiều tính năng hơn

Vì vậy, với điều kiện là bạn có thể thêm các cột JSON vào bảng, trích xuất các trường và nhận được tất cả tính linh hoạt của các trường JSON khi xác thực, sẽ không tốt hơn nếu chỉ lưu trữ tất cả dữ liệu của bạn trong các trường JSON thay vì các bảng được chuẩn hóa?

Vâng, đôi khi đó có thể là một ý tưởng tốt. Nhưng sau đó, bạn có thể sử dụng cơ sở dữ liệu NoQuery tốt hơn thay vì MySQL

Một lý do khác khiến việc sử dụng các trường JSON chủ yếu để lưu trữ dữ liệu của bạn không phải là một ý tưởng hay là nó có thể gặp khó khăn về hiệu suất

Chọn hiệu suất

Ví dụ: giả sử chúng tôi muốn chọn tất cả các sản phẩm có màu nâu. Chúng ta có thể sử dụng hàm JSON_EXTRACT trong mệnh đề WHERE mà chúng ta đã thấy trước đó trong hướng dẫn này

{
  "username": "jsmith",
  "location": "United States"
}

3

Hãy xem kế hoạch thực hiện cho điều này

Kế hoạch thực hiện hiển thị bước Quét toàn bộ bảng, đây là loại truy cập chậm. Điều này có thể ổn đối với bảng của chúng tôi, chỉ có một vài bản ghi, nhưng một khi bạn bắt đầu làm việc với các bảng lớn hơn, nó có thể khá chậm

Vì vậy, sử dụng hàm JSON_EXTRACT trong mệnh đề WHERE có nghĩa là quét toàn bộ bảng được sử dụng

Chúng ta có thể làm gì?

Một giải pháp để chọn dữ liệu

May mắn thay, MySQL cho phép bạn xác định một cột ảo trên bảng và tạo một chỉ mục trên cột ảo đó. Điều này sẽ làm cho truy vấn của chúng tôi nhanh hơn

Cột ảo là cột tính toán dựa trên một cột khác trong bảng

Hãy xem làm thế nào chúng ta có thể làm điều đó

Đầu tiên, chúng ta tạo một cột mới chứa thuộc tính color

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

6

Chúng ta có thể chọn từ bảng sản phẩm để xem

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

7idproduct_namecolorattributes1Ghế“đen”{“màu”. “đen”, “sâu”. “60cm”, “chiều rộng”. “100cm”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bảng“đen”{“màu”. “đen”, “sâu”. “100cm”, “chất liệu”. “gỗ”}3Desk“đen”{“màu”. “đen”, “ngăn kéo”. [{"cạnh". “trái”, “chiều cao”. “30cm”}, {“bên”. “trái”, “chiều cao”. “40cm”}], “chất liệu”. “kim loại”}4Bảng phụ“nâu”{“màu”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Bàn ăn “nâu”{“màu sắc”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}6Bàn lớn“trắng”{“màu”. “trắng”, “ngăn kéo”. ["{\"cạnh\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”bên\”. \”phải\”, \”chiều cao\”. \”50cm\”}”], “chất liệu”. "kim loại"}

Bây giờ, chúng ta có thể tạo một chỉ mục trên cột mới này

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

8

Bây giờ, hãy chọn lại từ bảng, lọc trên cột ảo thay vì trường JSON

INSERT INTO product [id, product_name, attributes]
VALUES [1, 'Chair', '{"color":"brown", "material":"wood", "height":"60cm"}'];

9

Lưu ý rằng bộ lọc trong mệnh đề WHERE bao gồm dấu ngoặc kép, vì đây là giá trị được trích xuất từ ​​trường JSON

The results are

idproduct_namecolorattributes4Side Tablebrown{“color”. “nâu”, “chất liệu”. [“kim loại”, “gỗ”]}5Nâu bàn ăn{“màu”. “nâu”, “chất liệu”. [“gỗ”, “kim loại”]}

Chúng ta có thể kiểm tra kế hoạch thực hiện để xem nó đã được chạy như thế nào

Chúng ta có thể thấy rằng bước này hiện được gọi là “Tra cứu khóa không phải duy nhất” và chỉ mục đã được sử dụng. Điều này hiệu quả hơn Quét toàn bộ bảng

Đã nói tất cả những điều đó, nếu bạn đang tạo các cột ảo để có thể truy cập dữ liệu trong các trường JSON của MySQL hiệu quả hơn chỉ để làm cho ứng dụng và cơ sở dữ liệu của bạn hoạt động, thì có lẽ trường JSON không phù hợp với cơ sở dữ liệu của bạn. Nhưng chỉ có bạn mới biết điều đó – mỗi trường hợp là khác nhau

 

Mẹo để làm việc với JSON trong MySQL

Trong hướng dẫn này, chúng ta đã xem JSON là gì, cách tạo các trường JSON trong MySQL và biết nhiều cách chúng ta có thể làm việc với chúng

Vì vậy, cách tốt nhất để làm việc với các trường JSON trong MySQL là gì?

Dưới đây là một số mẹo tôi có thể cung cấp để sử dụng JSON trong MySQL. Chúng có thể không áp dụng cho ứng dụng hoặc cơ sở dữ liệu của bạn nhưng chúng là những thứ cần xem xét

Chỉ vì bạn có thể, không có nghĩa là bạn nên

JSON linh hoạt và khá mạnh mẽ, nhưng chỉ vì bạn có thể lưu trữ dữ liệu trong trường JSON, không có nghĩa là bạn nên. Cân nhắc sử dụng các lợi thế của cơ sở dữ liệu quan hệ MySQL và sử dụng JSON khi thích hợp

Đối xử với trường JSON như một hộp đen

Trường JSON có thể được sử dụng để lưu trữ dữ liệu JSON hợp lệ do ứng dụng của bạn gửi hoặc nhận. Mặc dù có các chức năng để đọc và làm việc với trường JSON, tốt hơn hết là chỉ lưu trữ dữ liệu JSON trong trường, truy xuất dữ liệu đó từ ứng dụng của bạn và xử lý dữ liệu đó ở đó

Đây là khái niệm về hộp đen. Ứng dụng đưa dữ liệu vào và đọc dữ liệu từ nó và cơ sở dữ liệu không quan tâm đến những gì bên trong trường

Nó có thể hiệu quả hoặc không phù hợp với trường hợp của bạn, nhưng hãy cân nhắc áp dụng phương pháp này

Tìm kiếm theo Khóa chính và các trường khác

Chúng tôi đã thấy rằng việc tìm kiếm theo các thuộc tính bên trong trường JSON có thể chậm. Xem xét lọc theo khóa chính và các trường khác trong bảng, thay vì các thuộc tính bên trong trường JSON. Điều này sẽ giúp với hiệu suất

 

Sự kết luận

Tôi hy vọng bạn tìm thấy hướng dẫn này hữu ích. Bạn đã sử dụng các trường JSON trong MySQL chưa?