MySQL JSON data type decoding

In this post, we are going to explore the JSON data type in MySQL 5.7 and during the dive we will use the Laravel framework to build queries.




First, create a new table:
')

CREATE TABLE `products` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` JSON, `specs` JSON, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

And add some values:

 INSERT INTO products VALUES( null, '{"en": "phone", "it": "telefono"}', '{"colors": ["black", "white", "gold"], "size": {"weight": 1, "height": 1}}' ); INSERT INTO products VALUES( null, '{"en": "screen", "it": "schermo"}', '{"colors": ["black", "silver"], "size": {"weight": 2, "height": 3}}' ); INSERT INTO products VALUES( null, '{"en": "car", "it": "auto"}', '{"colors": ["red", "blue"], "size": {"weight": 40, "height": 34}}' ); 

Read JSON values

We can read JSON column values ​​using a simple syntax:

 select name->"$.en" as name, specs->"$.size.weight" as weight, specs->"$.colors" as colors from products; 

We get the following result:

name	weight	colors
'phone'	one	['black', 'white', 'gold']
'screen'	2	['black', 'silver']
'car'	40	['red', 'blue']

As you may have noticed, the results are obtained as a string in JSON format, this means that you need to decode them before displaying on the screen.

 json_decode( Products::selectRaw('name->"$.en" as name')->first()->name ) 

About syntax

Execution of requests in JSON format is done through the operator " -> ", on the left placing the name of the operator column, and on the right the syntax of the path.

To present a document in JSON format followed by a selector, the PATH syntax uses the leading $ to point to specific parts of the document. Here are various ways to extract data:

• specs -> "$. colors" will return an array of colors
• specs -> "$. colors [0]" will return the JSON string “black”
• specs -> "$. non_existing" returns NULL
• specs -> "$. 'key name with space'" if the key contains spaces

If the key is not a valid ECMAScript identifier, it must be enclosed in quotes inside the path.

Use of substitutions

You can also use a mask to request JSON values. Suppose we have the following data:

 {"name": "phone", "price": 400, "sizes": [3, 4, 5]} 

Syntax	Result	Note
specs -> "$. *"	['phone', [3, 4, 5], [{'name': 'black'}, {'name': 'gold'}]]]
specs -> "$. sizes [*]"	[3, 4, 5]	Same as $ .sizes
specs -> "$. colors **. name"	['black', 'gold']	The syntax “prefix ** suffix” will request all paths starting with the prefix and ending with the suffix.

Request value in JSON format

This works the same as in the usual MySQL columns. Now that we know how to write the right path to query and / or sort values ​​in JSON format, let's see some examples:

 select name->"$.en" from products where name->"$.en" = "phone"; select name->"$.en" from products where name->"$.en" IN ("phone"); select specs->"$.size.weight" from products where specs->"$.size.weight" BETWEEN 1 AND 10; select * from products ORDER BY name->"$.en"; 

JSON data type in MySQL and Laravel framework

If you use the Laravel framework version 5.2.23 or higher, you will be able to freely use the query designer to form a query in JSON format:

 Product::where('name->en', 'car')->first(); Product::whereIn('specs->size->weight', [1, 2, 3])->get(); Product::select('name->en')->orderBy('specs->size->height', 'DESC')->get(); 

If not, then you need to use RAW :

 Product::whereRaw('name->"$.en"', 'car')->first(); 

Conclusion

In many cases, developers prefer the NoSQL database for specific features, flexibility and / or performance, but SQL databases are preferred and many large companies rely on them when developing productive web applications using the MySQL + bundle (Mongo | Redis | etc.), but it adds complexity to the stack. With the introduction of the JSON data type in MySQL, it became a kind of hybrid SQL-NoSQL database.

From translator

In the examples, where you can see "Christmas trees" - you need to put "quotes". This Habr handles them so.