Back to home

MySQL JSON data type decoded

In this post we are going to explore the new MySQL 5.7 JSON Data Type. While diving into the topic we are going to use Laravel\'s fluent query builder.

First let's create a simple 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 now we insert 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}}'
);

Reading JSON values

Using a simple syntax, here's how we can read JSON values:

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

The result of this query is:

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

As you may have noticed the results are produced as JSON strings, that means you'll need to decode them before display.

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

About the syntax

Querying JSON fields is done via the -> operator, at the left hand side of the operator we add the column name, the right hand side we place the path syntax.

The Path syntax uses a leading $ to represent the JSON document followed by selectors that indicate more specific parts of the document. Here are the different paths to extract data:

  • specs->"$.colors" returns the array of colors.
  • specs->"$.colors[0]" returns a 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 then it must be quoted inside the path.

Using Wildcards

You may also use wild cards to query JSON values, imagine we have the following data:

{"name": "phone", "price": 400, "sizes": [3, 4, 5]}
Syntax Output Notes
specs->"$.*" ["phone", [3, 4, 5], [{"name": "black"}, {"name": "gold"}]]
specs->"$.sizes[*]" [3, 4, 5] Same as $.sizes
specs->"$.colors**.name" ["black", "gold"] The "prefix**suffix" syntax will query for all paths that begin with the prefix and ends with the suffix.

Querying JSON values

It works the same as in regular MySQL columns, now that we know how to write a valid path we'll be able to query and/or sort JSON values, here are various 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";

MySQL JSON Data Type and laravel

If you're using laravel 5.2.23 or above you'll be able to use the fluent query builder to query JSON data types:

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 can use raw statements:

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

Conclusion

In many cases developers prefer a NoSQL database for specific features, flexibility, or performance, however SQL databases are preferred by many developers and a lot of large companies rely on it building high performance web applications, so it happens a lot that one needs to use MySQL + (Mongo|Redis|etc...) but this adds complexity to the stack. With the introduction of a JSON data type in MySQL, it became sort of a SQL-NoSQL hybrid database.