JSON Data Type in MySQL 8 By Example

by Gotals Team · Published January 18, 2021 · Updated January 13, 2021

In this tutorial, you will learn how to use MySQL JSON data type to store JSON documents in the database. Select JSON documents, select specific node of JSON documents and more.

MySQL 8 supports the JSON data type. The JSON data type allows you to store JSON documents more efficiently than the JSON text format.

MySQL stores JSON documents in an internal format that allows quick read access to document elements.

The JSON binary format is structured in the way that permits the server to search for values within the JSON document directly by key or array index,

which is very fast.

While selecating a particular node from JSON data we have to use column path operator ( ->) i.e jsondata->’$.name’ name and we can also select the using inline path operator (->>) in i.e jsondata->>’$.name’ MySQL 8.

Difference Between ( ->) vs (->>) JSON Data Type in MySQL 8

CREATE TABLE events(

id int auto_increment primary key,

data json

);

INSERT INTO events(data)

VALUES (

‘{ “name”: “Safari”, “os”: “Mac”, “resolution”: { “x”: 1920, “y”: 1080 } }’

);

SELECT id, data->’$.name’ browser

FROM events;

Result:

+—-+———–+

| id | browser |

+—-+———–+

| 1 | “Safari” |

+—-+———–+

SELECT id, data->>’$.name’ browser

FROM events;

Result:

+—-+———–+

| id | browser |

+—-+———–+

| 1 | Safari |

+—-+———–+

Notice that data in the browser column is surrounded by quote marks when we are using column path operator ( ->). By using inline path operator (->>) you can remove the quote marks.

{Read:- 5 Ways to Improve SEO on Your WordPress Site }