{"id":2465,"date":"2018-07-23T13:13:57","date_gmt":"2018-07-23T12:13:57","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2465"},"modified":"2019-04-02T10:10:45","modified_gmt":"2019-04-02T09:10:45","slug":"30-mins-with-mysql-json-functions","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2018\/07\/23\/30-mins-with-mysql-json-functions\/","title":{"rendered":"30 mins with MySQL JSON functions"},"content":{"rendered":"

Lire cet article en fran\u00e7ais<\/a><\/em><\/p>\n

Note<\/span>: You may also be interested by\u00a030 mins with JSON in MySQL<\/a><\/em><\/p>\n

Note<\/em><\/span> 2: Handling JSON documents could be also done with MySQL Document Store<\/a>.<\/p>\n

JSON<\/a> (JavaScript Object Notation) is a popular way for moving data between various systems, including databases.\u00a0 Starting with 5.7\u00a0MySQL supports a native JSON data type (internal binary format for efficiency) and a set of built-in JSON functions that allows you to\u00a0perform operations on JSON documents.<\/p>\n

This blog post is not a complete overview of the entire MySQL JSON functions set (RTFM instead<\/a>) but rather an arbitrary<\/span> presentation of some of them.<\/p>\n

Note<\/span>: MySQL 8 enables\u00a0an alternative way of working with MySQL as a document store<\/a>. (Not cover in this blog post).<\/em><\/p>\n

I’m using MySQL 8.0<\/strong>.11, downloadable here<\/a>.<\/p>\n

\"MySQL<\/a><\/p>\n

JSON Utility Functions<\/h2>\n

JSON_PRETTY<\/h3>\n

Improve readability with JSON_PRETTY<\/a><\/p>\n

By default, display a JSON document in MySQL looks like something like this :<\/p>\n

SELECT doc FROM restaurants LIMIT 1\\G\n*************************** 1. row ***************************\ndoc: {\"_id\": \"564b3259666906a86ea90a99\", \"name\": \"Dj Reynolds Pub And Restaurant\", \"grades\": [{\"date\": {\"$date\": 1409961600000}, \"grade\": \"A\", \"score\": 2}, {\"date\": {\"$date\": 1374451200000}, \"grade\": \"A\", \"score\": 11}, {\"date\": {\"$date\": 1343692800000}, \"grade\": \"A\", \"score\": 12}, {\"date\": {\"$date\": 1325116800000}, \"grade\": \"A\", \"score\": 12}], \"address\": {\"coord\": [-73.98513559999999, 40.7676919], \"street\": \"West   57 Street\", \"zipcode\": \"10019\", \"building\": \"351\"}, \"borough\": \"Manhattan\", \"cuisine\": \"Irish\", \"restaurant_id\": \"30191841\"}<\/pre>\n
You can have a prettier display with JSON_PRETTY<\/strong> :<\/p>\n
SELECT JSON_PRETTY(doc) FROM restaurants LIMIT 1\\G\n*************************** 1. row ***************************\nJSON_PRETTY(doc): {\n  \"_id\": \"564b3259666906a86ea90a99\",\n  \"name\": \"Dj Reynolds Pub And Restaurant\",\n  \"grades\": [\n    {\n      \"date\": {\n        \"$date\": 1409961600000\n      },\n      \"grade\": \"A\",\n      \"score\": 2\n    },\n    {\n      \"date\": {\n        \"$date\": 1374451200000\n      },\n      \"grade\": \"A\",\n      \"score\": 11\n    },\n    {\n      \"date\": {\n        \"$date\": 1343692800000\n      },\n      \"grade\": \"A\",\n      \"score\": 12\n    },\n    {\n      \"date\": {\n        \"$date\": 1325116800000\n      },\n      \"grade\": \"A\",\n      \"score\": 12\n    }\n  ],\n  \"address\": {\n    \"coord\": [\n      -73.98513559999999,\n      40.7676919\n    ],\n    \"street\": \"West   57 Street\",\n    \"zipcode\": \"10019\",\n    \"building\": \"351\"\n  },\n  \"borough\": \"Manhattan\",\n  \"cuisine\": \"Irish\",\n  \"restaurant_id\": \"30191841\"\n}<\/pre>\n
JSON_STORAGE_SIZE<\/h3>\n
Return the number of bytes used to store the binary representation of a JSON document with\u00a0JSON_STORAGE_SIZE<\/a>.<\/p>\n
SELECT max(JSON_STORAGE_SIZE(doc)) FROM restaurants;\n+-----------------------------+\n| max(JSON_STORAGE_SIZE(doc)) |\n+-----------------------------+\n|                         916 |\n+-----------------------------+\n\nSELECT avg(JSON_STORAGE_SIZE(doc)) FROM restaurants;\n+-----------------------------+\n| avg(JSON_STORAGE_SIZE(doc)) |\n+-----------------------------+\n|                    537.2814 |\n+-----------------------------+\n\nSELECT min(JSON_STORAGE_SIZE(doc)) FROM restaurants;\n+-----------------------------+\n| min(JSON_STORAGE_SIZE(doc)) |\n+-----------------------------+\n|                         255 |\n+-----------------------------+<\/pre>\n
In this collection, the heavier document is 916 bytes, the lighter is 255 and the average size is 537.2814<\/p>\n
Note<\/span>:\u00a0This is the space used to store the JSON document as it was inserted into the column, prior to any partial updates<\/a> that may have been performed on it afterwards.<\/em><\/p>\n
Functions That Search JSON Values<\/h2>\n
JSON_EXTRACT (->) \/ JSON_UNQUOTE \/ ->> operator<\/h3>\n
JSON_EXTRACT<\/a> (or -><\/a>) returns data from a JSON document.<\/p>\n
JSON_UNQUOTE<\/a> unquotes JSON value and returns the result as a utf8mb4 string.<\/p>\n
->><\/a> the JSON unquoting extraction operator is a shortcut for JSON_UNQUOTE(JSON_EXTRACT())<\/p>\n
SELECT JSON_EXTRACT(doc, \"$.cuisine\") FROM restaurants LIMIT 1\\G\n*************************** 1. row ***************************\nJSON_EXTRACT(doc, \"$.cuisine\"): \"Irish\"\n\n\nSELECT doc->\"$.cuisine\" FROM restaurants LIMIT 1\\G\n*************************** 1. row ***************************\ndoc->\"$.cuisine\": \"Irish\"<\/pre>\n
Both queries above are similar.<\/p>\n
If you want the same result but without quotes use\u00a0->><\/strong> or JSON_UNQUOTE<\/strong>(JSON_EXTRACT<\/strong>()) :<\/p>\n
SELECT JSON_UNQUOTE(JSON_EXTRACT(doc, \"$.cuisine\")) FROM restaurants LIMIT 1\\G\n*************************** 1. row ***************************\nJSON_UNQUOTE(JSON_EXTRACT(doc, \"$.cuisine\")): Irish\n\n\nSELECT doc->>\"$.cuisine\" FROM restaurants LIMIT 1\\G\ndoc->>\"$.cuisine\": Irish<\/pre>\n
Both queries above are similar.<\/p>\n
JSON_CONTAINS<\/h3>\n
Search whether the value of specified key matches a specified value with\u00a0JSON_CONTAINS<\/a>.<\/p>\n
SELECT count(*) \nFROM restaurants \nWHERE JSON_CONTAINS(doc, '\"Creole\"', '$.cuisine');\n+----------+\n| count(*) |\n+----------+\n|       24 |\n+----------+\n\n\nSELECT doc->>\"$.name\" \nFROM restaurants \nWHERE JSON_CONTAINS(doc, '\"Creole\"', '$.cuisine');\n+-----------------------------------------------+\n| doc->>\"$.name\"                                |\n+-----------------------------------------------+\n| Belvedere Restaurant                          |\n| Chez Macoule Restaurant                       |\n| Paradise Venus Restaurant                     |\n| Heavenly Fritaille Restaurant                 |\n| Yolie'S Bar & Restaurant                      |\n| Yo-Yo Fritaille                               |\n| Kal Bakery & Restaurant                       |\n| Bon Appetit Restaurant                        |\n| Katou Fin Restaurant                          |\n| Alhpa Restaurant                              |\n| Lakay Buffet Restaurant                       |\n| La Tranquilite Restaurant                     |\n| La Caye Restaurant                            |\n| Nous Les Amis Restaurant & Bakery             |\n| Yoyo Fritaille                                |\n| Fresh Crown Restaurant                        |\n| Tonel Restaurant & Lounge                     |\n| Grace Devine Pastry And Restaurant Restaurant |\n| Viva Bubble Tea                               |\n| Cafe Creole Restaurant N Bakery               |\n| Delly'S Place Restaurant & Fritaille          |\n| Creole Plate                                  |\n| Chez Nous Restaurant & Fritaille              |\n| Combite Creole                                |\n+-----------------------------------------------+<\/pre>\n
JSON_CONTAINS_PATH<\/h3>\n
Indicate whether a JSON document contains data at a given path or paths with JSON_CONTAINS_PATH<\/a>.<\/p>\n
Let’s insert a dummy document in the collection restaurants<\/p>\n
INSERT INTO restaurants (doc) VALUES ('{\"_id\": \"1234\", \"name\": \"Daz Restaurant\", \"cuisine\": \"West Indian\", \"restaurant_id\": \"4321\"}');<\/pre>\n
How many documents without grades? :<\/p>\n
SELECT count(*), JSON_CONTAINS_PATH(doc, 'one', '$.grades') cp \nFROM restaurants \nGROUP BY cp;\n+----------+------+\n| count(*) | cp   |\n+----------+------+\n|        1 |    0 |\n|    25359 |    1 |\n+----------+------+<\/pre>\n
Ok, only 1. We can easily check the structure of this document :<\/p>\n
SELECT JSON_PRETTY(doc) \nFROM restaurants \nWHERE JSON_CONTAINS_PATH(doc, 'one', '$.grades') = 0\\G\n*************************** 1. row ***************************\nJSON_PRETTY(doc): {\n  \"_id\": \"1234\",\n  \"name\": \"Daz Restaurant\",\n  \"cuisine\": \"West Indian\",\n  \"restaurant_id\": \"4321\"\n}<\/pre>\n
A bridge between 2 models<\/h2>\n
To paraphrase David Stokes<\/a> (MySQL Community Manager) in his book MySQL and JSON – A practical Programming Guide<\/strong><\/a>.<\/p>\n
\u00ab\u00a0<\/em><\/p>\n
The advantages of traditional relational data and schemaless data are both large. But in some cases, data in a schema needs to be schemaless, or schemaless-data needs to be in a schema.\u00a0<\/em><\/p>\n
\u00ab\u00a0<\/em><\/p>\n
Making such metamorphosis is very easy to do with MySQL!<\/p>\n
Relational to JSON<\/h3>\n
JSON_OBJECT<\/h4>\n
Evaluates a list of key-value pairs and returns a JSON object containing those pairs with\u00a0JSON_OBJECT<\/a>.<\/p>\n
A traditional SQL query with a relational result set. The JSON document output non-JSON data :<\/p>\n
SELECT doc->>\"$.name\" \nFROM restaurants \nWHERE JSON_CONTAINS(doc, '\"Creole\"', '$.cuisine') \nLIMIT 2;\n+-------------------------+\n| doc->>\"$.name\"          |\n+-------------------------+\n| Belvedere Restaurant    |\n| Chez Macoule Restaurant |\n+-------------------------+<\/pre>\n
This result set could be convert in a JSON format, actually a JSON object :<\/p>\n
SELECT JSON_OBJECT(\"Name\", doc->>\"$.name\") \nFROM restaurants \nWHERE JSON_CONTAINS(doc, '\"Creole\"', '$.cuisine') \nLIMIT 2;\n+-------------------------------------+\n| JSON_OBJECT(\"Name\", doc->>\"$.name\") |\n+-------------------------------------+\n| {\"Name\": \"Belvedere Restaurant\"}    |\n| {\"Name\": \"Chez Macoule Restaurant\"} |\n+-------------------------------------+<\/pre>\n
Other example :<\/p>\n
SELECT Name, Population \nFROM City \nWHERE CountryCode='fra' \nORDER BY Population DESC \nLIMIT 5;\n+-----------+------------+\n| Name      | Population |\n+-----------+------------+\n| Paris     |    2125246 |\n| Marseille |     798430 |\n| Lyon      |     445452 |\n| Toulouse  |     390350 |\n| Nice      |     342738 |\n+-----------+------------+\n\n\nSELECT JSON_OBJECT(\"CityName\",Name, \"CityPop\", Population) \nFROM City \nWHERE CountryCode='fra' \nORDER BY Population DESC \nLIMIT 5;\n+-----------------------------------------------------+\n| JSON_OBJECT(\"CityName\",Name, \"CityPop\", Population) |\n+-----------------------------------------------------+\n| {\"CityPop\": 2125246, \"CityName\": \"Paris\"}           |\n| {\"CityPop\": 798430, \"CityName\": \"Marseille\"}        |\n| {\"CityPop\": 445452, \"CityName\": \"Lyon\"}             |\n| {\"CityPop\": 390350, \"CityName\": \"Toulouse\"}         |\n| {\"CityPop\": 342738, \"CityName\": \"Nice\"}             |\n+-----------------------------------------------------+<\/pre>\n
JSON_OBJECTAGG<\/h4>\n
Takes two column names or expressions and returns a JSON object containing key-value pairs with\u00a0JSON_OBJECTAGG<\/a>.<\/p>\n
Grouping rows are very often useful. This why we implemented some JSON aggregate functions like this one.<\/p>\n
SELECT JSON_OBJECTAGG(Name, CountryCode) \nFROM City  \nGROUP BY id \nORDER BY RAND() \nLIMIT 5;\n+-----------------------------------+\n| JSON_OBJECTAGG(Name, CountryCode) |\n+-----------------------------------+\n| {\"Reno\": \"USA\"}                   |\n| {\"Hanam\": \"KOR\"}                  |\n| {\"Laizhou\": \"CHN\"}                |\n| {\"Yogyakarta\": \"IDN\"}             |\n| {\"Tantoyuca\": \"MEX\"}              |\n+-----------------------------------+<\/pre>\n