MySQL JSON

If you are interested to learn about the MySQL JSON

JSON abbreviated as JavaScript Object Notation. It is a lightweight data-interchange format similar to other data types and can be easily read and write by humans. It can also be parsed and generate by machines easily. Generally, the JSON data type supports two structures:

A collection of name/value pairs chain, which acts as a data array.
An ordered list of values.

Since it manages the individual values in a name-value pair chain that acts as a data array, we can retrieve the whole field using a single command. This useful feature allows us to retrieve the data in a large system quickly. MySQLprovides supports for native JSON data type from version 5.7.8 that stores JSON document in an internal format, which enables quick and efficient read access to document objects. This data type can store JSON documents more accurately than the J S ON text format we had used in the past MySQL versions.

The following are the advantages that the JSON data type gives over storing JSON-format strings:

The JSON columns allow us to store the automatic validation of JSON documents. Otherwise, we will get an error.
Optimized/Fast storage format means when the server reads a JSON value stored in binary format, it does not need to parse from a text representation. The binary format allows searching values within JSON documents directly with a key or array indexes without reading whole values.

The storage space required for the JSON document is roughly the same as the storage requirements for LONGBLOB and LONGTEXT.

We can define the JSON data type column in the MySQL table using the following index:

CREATE TABLE table_name (  
    ...  
    json_column_name JSON,  
    ...   
);

NOTE: It is to note that we cannot store a non-null default value in the JSON column. Also, the JSON column cannot be indexed directly because it creates an index by extracting a scalar value from the JSON column. If we want to retrieve data from the JSON column, the MySQL optimizer searches compatible indexes that match the JSON expressions.

Why we use JSON?

We will use the JSON data type in MySQL because of its use-cases, where we can use a make-shift approach. Let us understand it with the help of an example.

Suppose we are creating a web application and want to save a user’s configurations or preferences in the table. Generally, we used to create a separate table that contains user_id, key, and value fields or saves it as a formatted string so that it can be parsed at runtime. This method is good for limited users. If the user’s list will be large and more configuration/preference keys, this method is not good. To overcome these issues, MySQL allows us to use a JSON data type field for storing the user’s configurations or preferences that saves the table’s space and stores the records separately, which will be the same as the number of visitors who visits the website.

Does MySQL support JSON?

MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: Automatic validation of JSON documents stored in JSON columns.

Is MySQL JSON efficient?

Efficient Access – More importantly, when you store a JSON document in a JSON column, it is not stored as a plain text value. Instead, it is stored in an optimized binary format that allows for quicker access to object members and array elements

Adding JSON Data

Whole JSON documents can be passed in INSERT or UPDATE statements. For example, our book tags can be passed as an array (inside a string):

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

JSON can also be created with these:

JSON_ARRAY() function, which creates arrays. For example:-- returns [1, 2, "abc"]: SELECT JSON_ARRAY(1, 2, 'abc');
JSON_OBJECT() function, which creates objects. For example:-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT('a', 1, 'b', 2);
JSON_QUOTE() function, which quotes a string as a JSON value. For example:-- returns "[1, 2, \"abc\"]": SELECT JSON_QUOTE('[1, 2, "abc"]');
or you can (CAST anyValue AS JSON).

The JSON_TYPE() function allows you to check JSON value types. It should return OBJECT, ARRAY, a scalar type (INTEGER, BOOLEAN, etc), NULL, or an error. For example:

-- returns ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');

The JSON_VALID() function returns 1 if the JSON is valid or 0 otherwise:

-- returns 1:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns 1:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns 0:
SELECT JSON_TYPE('{"a": 1, "b": 2');

Attempting to insert an invalid JSON document will raise an error and the whole record will not be inserted/updated.

Searching JSON Data

The JSON_CONTAINS() function accepts the JSON document being searched and another to compare against. It returns 1 when a match is found. For example:

-- all books with the 'JavaScript' tag:
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

The similar JSON_SEARCH() function returns the path to the given match or NULL when there’s no match. It’s passed the JSON document being searched, 'one' to find the first match, or 'all' to find all matches, and a search string (where % matches any number of characters and _ matches one character in an identical way to like. For example:

-- all books with tags starting 'Java':
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

JSON Paths

A JSON path targets values and can be used to extract or modify parts of a JSON document. The JSON_EXTRACT() function demonstrates this by extracting one or more values:

-- returns "SitePoint":
SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

All path definitions start with a $ followed by other selectors:

a period followed by a name, such as $.website
[N] where N is the position in a zero-indexed array
the .[*] wildcard evaluates all members of an object
the [*] wildcard evaluates all members of an array
the prefix**suffix wildcard evaluates to all paths that begin with the named prefix and end with the named suffix

The following examples refer to the following JSON document:

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

Example paths:

$.a returns 1
$.c returns [3, 4]
$.c[1] returns 4
$.d.e returns 5
$**.e returns [5]

Extracting JSON Paths in Queries

You could extract the name and first tag of your book table using the query:

SELECT
  title, tags->"$[0]" AS `tag1`
FROM `book`;

For a more complex example, presume you have a user table with JSON profile data. For example:

id	name	profile
1	Craig	{ “email”: [“craig@email1.com”, “craig@email2.com”], “twitter”: “@craigbuckler” }
2	SitePoint	{ “email”: [], “twitter”: “@sitepointdotcom” }

You can extract the Twitter name using a JSON path. For example:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

You could use a JSON path in the WHERE clause to only return users with a Twitter account:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

Modifying Part of a JSON Document

There are several MySQL functions to modify parts of a JSON document using path notation. These include:

JSON_SET(doc, path, val[, path, val]...): inserts or updates data in the document
JSON_INSERT(doc, path, val[, path, val]...): inserts data into the document
JSON_REPLACE(doc, path, val[, path, val]...): replaces data in the document
JSON_MERGE(doc, doc[, doc]...): merges two or more document
JSON_ARRAY_APPEND(doc, path, val[, path, val]...): appends values to the end of an array
JSON_ARRAY_INSERT(doc, path, val[, path, val]...): inserts an array within the document
JSON_REMOVE(doc, path[, path]...): removes data from the document

You can therefore add a “technical” tag to any book which already has a “JavaScript” tag:

UPDATE `book`
  SET tags = JSON_MERGE(tags, '["technical"]')
WHERE
  JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

Further Information

The MySQL manual provides further information about the JSON data type and the associated JSON functions.

Again, I urge you not to use JSON unless it’s absolutely necessary. You could emulate an entire document-oriented NoSQL database in MySQL, but it would negate many benefits , and you may as well switch to a real NoSQL system! That said, JSON data types might save effort for more obscure data requirements within an SQL application.

More MySQL JSON Data Type Example

Suppose we want to track the user and their actions who visit our website, such as some users only see the pages and others would visit the page and buy the products. Let’s create a new table called “events” that will store this information using the following statement:

CREATE TABLE events(   
  event_id INT AUTO_INCREMENT PRIMARY KEY,   
  event_name varchar(75),   
  visitors varchar(25),   
  properties json,   
  browser_name json  
);

Event id is used to uniquely identify each event in the events table. The event name stores the name of an event such as page-view, purchase, etc. The visitor column stores the user information who visits the websites, and properties and browser_name columns are used to keep the JSON values. Browser_name column stores the browser’s specifications such as browser name, operating system, resolution, etc. that visitors use to browse the website.

Next, we will insert the data into this table using given below:

INSERT INTO events (event_name, visitors, properties, browser_name)   
VALUES (  
  'page-view',   
   '2',  
   '{"page": "/"}',  
   '{ "name": "Google Chrome", "OS": "Windows", "resolution": {"x": 1920, "y": 1080} }'  
),  
('page-view',   
  '3',  
  '{"page": "/products"}',  
  '{ "name": "Safari", "OS": "UNIX", "resolution": {"x": 2560, "y": 1600} }'  
),  
(  
  'page-view',   
  '1',  
  '{"page": "/contacts"}',  
  '{ "name": "Mozilla Firefox", "OS": "Mac", "resolution": {"x": 1920, "y": 1080} }'  
),  
(  
  'purchase',   
   '4',  
  '{"amount": 250}',  
  '{ "name": "Google Chrome", "OS": "Windows", "resolution": {"x": 1280, "y": 800} }'  
),  
(  
  'purchase',   
   '3',  
  '{"amount": 350}',  
  '{ "name": "Safari", "OS": "Mac", "resolution": {"x": 1600, "y": 900} }'  
),  
(  
  'purchase',   
  '4',  
  '{"amount": 400}',  
  '{ "name": "Mozilla Firefox", "OS": "Windows", "resolution": {"x": 1280, "y": 800} }'  
);

Now, we can verify the table, execute like below:

mysql> SELECT * FROM events;

We will see the output as follows:

Suppose we want to retrieve any particular values such as browser name from the JSON columns. We can filter these types of the result by using the column path operator (->). See the below query:

mysql> SELECT event_id, browser_name->'$.name' AS browser FROM events;

It will return the below output:

In this image, you will notice that double quotation marks surround the browser column’s data. If you want to get the browser column’s data without any quotation marks, we need to use the inline path operator (->>) as follows:

mysql> SELECT event_id, browser_name->>'$.name' AS browser FROM events;

In the below output, we can see that the quotation marks have been removed successfully.

If we want to retrieve the browser usage, we can use the statement as follows:

mysql> SELECT browser_name->>'$.name' AS browser, count(browser_name)  
    FROM events GROUP BY browser_name->>'$.name';

We will get the below result:

If we want to calculate the visitor’s total revenue, we can use the query as follows:

mysql> SELECT visitors, SUM(properties->>'$.amount') As total_revenue  
FROM events WHERE properties->>'$.amount' > 0 GROUP BY visitors;

We will get the result as follows: