Khóa CẬP NHẬT JSON của MySQL

If you want to download a PDF version of this guide, enter your email below and you’ll receive it shortly

Mục lục

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?

If you need a structure that’s flexible

A normalised database structure, one with tables and columns and relationships, works well for most cases. Recent improvements in development practices also mean that altering a table is not as major as it was in the past, so adjusting your database once it’s in production is possible

However, if your requirements mean that your data structure needs to be flexible, then a JSON field may be good for your database

One example may be where a user can add custom attributes. If it was done using a normalised database, this may involve altering tables, or creating an Entity Attribute Value design, or some other method

If a JSON field was used for this, it would be much easier to add and maintain these custom attributes

The JSON data can also be stored in your database and processed by an ORM (Object Relational Mapper) or your application code, so your database may not need to do any extra work

 

What Does JSON Data Look Like?

Here’s a simple example of JSON data

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

It uses a combination of different brackets, colons, and quotes to represent your data

Let’s take a look at some more examples

 

Name/Value Pair

JSON data is written as name/value pairs. A name/value pair is two values enclosed in quotes

This is an example of a name/value pair

"username": "jsmith"

The name is “username” and the value is “jsmith”. They are separated by a colon “. ”

This means for the attribute of username, the value is jsmith. Names in JSON need to be enclosed in double quotes

 

Các đối tượng

JSON data can be enclosed in curly brackets which indicate it’s an object

{"username": "jsmith"}

This is the same data as the earlier example, but it’s now an object. This means it can be treated as a single unit by other areas of the application

How does this help? It’s good for when there are multiple attributes

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

Additional attributes, or name/value pairs, can be added by using a comma to separate them

You’ll also notice in this example the curly brackets are on their own lines and the data is indentented. This is optional. it’s just done to make it more readable

 

Arrays

JSON also supports arrays, which is a collection of records within an object. Arrays in JSON are included in square brackets and have a name

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

In this example, this object has an attribute called “posts”. The value of posts is an array, which we can see by the opening square bracket “[“

Inside the square bracket, we have a set of curly brackets, indicating an object, and inside those we have an id of 1 and a title of Welcome. We have another set of curly brackets indicating another object

These two objects are posts and they are contained in an array

And that covers the basics of what JSON is

If JSON is new to you, don’t worry, it gets easier as you work with it more

If you’re experienced with JSON, you’ll find the rest of the guide more useful as we go into the details of working with JSON in MySQL

 

How to Create and Populate JSON Field in MySQL

So you’ve learned a bit about JSON data and why you might want to use it

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

Creating a JSON Field

We create a new field with a data type of JSON

Here’s an example

CREATE TABLE product (
  id INT,
  product_name VARCHAR(200),
  attributes JSON
);

We have created a table called product. It has an id and a product name. There’s also an attributes column, which has the data type of JSON

Adding a JSON column is as easy as that

You might be thinking, if JSON data is just text data, why do we need to do anything special? Can’t we just create a large CLOB or VARCHAR field and add the JSON data to it?

We could, but the main benefit of a JSON data type is the format validation. With a simple CLOB field, we can store a text value. However, it means that we can store JSON that’s invalid. missing attribute names or curly brackets

With a JSON field, the data is automatically validated for us. We won’t be able to store invalid data in the table

Also, we get to use the various MySQL JSON functions on the JSON data to make working with it easier

Adding Data to a JSON Field

Now we’ve got our JSON field, how do we add data to it?

There are a few ways. 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

Các câu lệnh INSERT sẽ hoạt động và dữ liệu sẽ trông như thế này

SELECT
id,
product_name,
attributes
FROM product;

idproduct_nameattributes1Chair{“color”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bảng{“màu”. “nâu”, “chiều cao”. “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

The JSON_ARRAY function in MySQL lets you easily specify array data when inserting JSON data in 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”. “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”]}5Dining Table{“color”. “brown”, “material”. [“wood”, “metal”]}6Large Desk{“color”. “white”, “drawers”. [“{\”side\”. \”left\”, \”height\”. \”50cm\”}”, “{\”side\”. \”right\”, \”height\”. \”50cm\”}”], “material”. “metal”}

The new records with the array data are shown, and the arrays are enclosed in square brackets

We can see the last row, with an id of 6, has backslashes in its value. This is due to the fact an array object is used for an attribute. We’ll see how to display this better later

 

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

Once you’ve got some JSON data in a table, the next step is to read it

How do we do that?

We can run a simple SELECT statement to see the data in the table

SELECT
id,
product_name,
attributes
FROM product;

idproduct_nameattributes1Chair{“color”. “brown”, “height”. “60cm”, “material”. “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”]}5Dining Table{“color”. “brown”, “material”. [“wood”, “metal”]}6Large Desk{“color”. “white”, “drawers”. [“{\”side\”. \”left\”, \”height\”. \”50cm\”}”, “{\”side\”. \”right\”, \”height\”. \”50cm\”}”], “material”. “metal”}

This shows us the data in the JSON column, and it looks just like a text value

The good thing with this is that any application can easily read this field and work with it how they want (display it, filter it, and so on)

What if we wanted to do more in our database?

Selecting Individual Attributes

The JSON data is stored in something that looks like a text field. However, it’s quite easy to get attributes and values out of this text field and display them

We can extract a value from the JSON field and display it in a separate column. We do this using a combination of “path expressions” and the JSON_EXTRACT function

We need to use the JSON_EXTRACT function to search for a particular attribute in the JSON value. And we need to use a “path expression” to specify the attribute

How do we do this?

First, let’s write the path expression, and then put that into our JSON_EXTRACT function

The path expression lets us specify the attribute we want to search for. It starts with a $ symbol, and we specify a dot then the name of the attribute we’re looking for

For example, to specify the “color” attribute, out path expression would look like this

"username": "jsmith"

7

To specify the “material” attribute, we can use this path expression

"username": "jsmith"

8

If we had a height attribute enclosed in a dimensions attribute, our path expression would look like this

"username": "jsmith"

9

We use the dot to specify the next level in the hierarchy of attributes

How do we use this to filter our data? We combine this path expression with the JSON_EXTRACT function

The JSON_EXTRACT function takes two parameters

{"username": "jsmith"}

0

We can use this in an example

Displaying a Field using JSON_EXTRACT

Let’s say we want to display the color attribute in a separate column in our results

First, we write the path expression for the color attribute

"username": "jsmith"

7

Then, we add this to our JSON_EXTRACT function

{"username": "jsmith"}

2

Finally, we add this to our SELECT clause to show it as a separate column

{"username": "jsmith"}

3

This query will show all records, and show the color attribute as a separate column

idproduct_namecolorattributes1Chair“brown”{“color”. “nâu”, “chiều cao”. “60cm”, “material”. “wood”}2Table“brown”{“color”. “brown”, “height”. “110cm”, “material”. “wood”}3Desk“black”{“color”. “đ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”. ["{\"bên\". \”trái\”, \”chiều cao\”. \”50cm\”}”, “{\”side\”. \”right\”, \”height\”. \”50cm\”}”], “material”. "kim khí"}

We can see the separate column here

Path Expression Examples

We can see another field using JSON_EXTRACT by specifying the attribute name

{"username": "jsmith"}

4

Here we are extracting the attribute called height. This is available in some records but not others

idproduct_nameheightattributes1Chair“60cm”{“color”. “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”. “brown”, “height”. “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”]}6Bàn lớn“metal”{“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 khí"}

Điều gì sẽ xảy ra nếu chúng ta muốn xem một thuộc tính bên trong một thuộc tính khác?

Vì “ngăn kéo” là một mảng nên chúng ta không thể sử dụng ký hiệu dấu chấm để lấy thuộc tính như thế này

{"username": "jsmith"}

6

Điều này sẽ trả về giá trị null vì không có thuộc tính nào được gọi là side. nó là một phần của một mảng

Tuy nhiên, chúng ta có thể sử dụng một số để tham chiếu vị trí trong mảng

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.

Vì vậy, truy vấn của chúng tôi để trích xuất mục đầu tiên trong mảng là

{"username": "jsmith"}

8

Kết quả là

idproduct_namedrawerattributes1Chairnull{“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”. “wood”}3Desk{“side”. “trái”, “chiều cao”. “30cm”}{“màu”. “đen”, “ngăn kéo”. [{"bên". “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”. ["{\"bên\". \”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

Kết quả là

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”]}

This shows the same result as using the actual function name

Đ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_namecolorattributes1Chairbrown{“color”. “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
• con đường. 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”, “material”. “gỗ”}3Desk0{“màu”. “đen”, “ngăn kéo”. [{"bên". “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”. ["{\"bên\". \”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à 40 cm 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”. ["{\"bên\". \”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

Kết quả là

idproduct_namesearch_resultattributes1Chair“$. màu”{“màu”. “nâu”, “chiều cao”. “60cm”, “chất liệu”. “gỗ”}2Bàn“$. màu”{“màu”. “brown”, “height”. “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”. ["{\"bên\". \”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". Đâ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

Kết quả là

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”. [{"bên". “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 khí"}

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

Kết quả là

idproduct_namejtypeattributes1ChairOBJECT{“color”. “black”, “depth”. “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
• con đường. đườ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

If you want to replace the value of an attribute inside a JSON field with another value, you can use the JSON_REPLACE function

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
• con đường. đườ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
• con đường. đườ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_nameattributes2Table{“color”. “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ỏ

There’s no need to specify a value like the other JSON functions

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_nameattributes2Table{“color”. “đ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. We can use the JSON_EXTRACT function in the WHERE clause that we saw earlier in this guide

{
  "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ì?

One Solution to Selecting Data

Fortunately, MySQL allows you to define a virtual column on the table, and create an index on that virtual column. This should make our query faster

A virtual column is a column that is a calculation based on another column in the table

Let’s see how we can do that

First, we create a new column that contains the color attribute

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

6

We can select from the product table to see it

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

7idproduct_namecolorattributes1Chair“black”{“color”. “black”, “depth”. “60cm”, “chiều rộng”. “100cm”, “height”. “60cm”, “material”. “wood”}2Table“black”{“color”. “black”, “depth”. “100cm”, “chất liệu”. “wood”}3Desk“black”{“color”. “black”, “drawers”. [{“side”. “left”, “height”. “30cm”}, {“side”. “left”, “height”. “40cm”}], “material”. “metal”}4Side Table“brown”{“color”. “brown”, “material”. [“metal”, “wood”]}5Dining Table“brown”{“color”. “brown”, “material”. [“wood”, “metal”]}6Large Desk“white”{“color”. “white”, “drawers”. [“{\”side\”. \”left\”, \”height\”. \”50cm\”}”, “{\”side\”. \”right\”, \”height\”. \”50cm\”}”], “material”. “metal”}

Now, we can create an index on this new column

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

8

Now, let’s select from the table again, filtering on the virtual column instead of the JSON field

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

9

Notice that the filter in the WHERE clause includes double quotes, as this is the value that was extracted from the JSON field

Kết quả là

idproduct_namecolorattributes4Side Tablebrown{“color”. “brown”, “material”. [“metal”, “wood”]}5Dining Tablebrown{“color”. “brown”, “material”. [“wood”, “metal”]}

We can check the execution plan to see how it was run

We can see that the step is now called “Non-Unique Key Lookup” and the index was used. This is more efficient than the Full Table Scan

Having said all of that, if you’re creating virtual columns to be able to access data in MySQL JSON fields more efficiently just to make your application and database work, then perhaps the JSON field is not right for your database. But only you would know that – each case is different

 

Tips for Working with JSON in MySQL

In this guide, we’ve looked at what JSON is, seen how to create JSON fields in MySQL, and seen a range of ways we can work with them

So, what’s the best way to work with JSON fields in MySQL?

Here are some tips I can offer for using JSON in MySQL. They may not apply to your application or database but they are things to consider

Just because you can, doesn’t mean you should

JSON is flexible and quite powerful, but just because you can store data in a JSON field, doesn’t mean you should. Consider using the advantages of MySQL relational database and using JSON where appropriate

Treat the JSON field like a black box

The JSON field can be used to store valid JSON data sent or received by your application. While there are functions for reading from and working with the JSON field, it might be better to just store the JSON data in the field, retrieve it from your application, and process it there

This is the concept of a black box. The application puts data in and reads data from it, and the database doesn’t care about what’s inside the field

It may or may not work for your situation, but consider taking this approach

Search by the Primary Key and other fields

We’ve seen that it can be slow to search by attributes inside the JSON field. Consider filtering by the primary key and other fields in the table, rather than attributes inside the JSON field. This will help with performance

 

Conclusion

I hope you found this guide useful. Have you used JSON fields in MySQL? What has your experience been like? Do you have any questions? Feel free to use the comments section below

How to update JSON key value in MySQL?

How to update a value in JSON column in SQL?

How to update JSON array value in SQL?

Is JSON_INSERT used to add a new key and value?