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

Read this post in English<\/a><\/em><\/p>\n

Note<\/span>: L’article suivant peut t\u2019int\u00e9resser \u00e9galement:\u00a030 mins avec JSON en MySQL<\/a>.<\/em><\/p>\n

Note<\/em><\/span> 2: Tu peux \u00e9galement manipuler tes documents JSON avec MySQL Document Store<\/a>.<\/p>\n

Comme tu le sais,\u00a0JSON<\/a> (JavaScript Object Notation) est un populaire format d’\u00e9change de donn\u00e9es. Depuis la version 5.7<\/strong>, MySQL supporte un type de donn\u00e9es JSON\u00a0natif (au format interne binaire pour des raisons d’efficacit\u00e9s), ainsi qu’un riche ensemble de fonctions qui te permettront de manipuler dans tout les sens tes documents JSON.<\/p>\n

Soyons clair! Cet article n’est pas une revue exhaustive des diff\u00e9rentes fonctions JSON impl\u00e9ment\u00e9es dans MySQL (RTFM! \ud83d\ude09<\/a>), mais plut\u00f4t une s\u00e9lection arbitraire de certaines d’entre elles.<\/p>\n

Note<\/span>: Depuis MySQL 8 il est d\u00e9sormais possible de manipuler les documents JSON sans SQL (NoSQL) avec\u00a0MySQL as a document store<\/a>. (Cette fonctionnalit\u00e9s\u00a0n’est pas couverte dans cet article).<\/em><\/p>\n

Les exemples ci dessous sont r\u00e9alis\u00e9s sans trucage avec\u00a0MySQL 8.0<\/strong>.11, t\u00e9l\u00e9chargeable\u00a0ici<\/a>.<\/p>\n

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

JSON – Fonctions utilitaires<\/h2>\n

JSON_PRETTY<\/h3>\n

Am\u00e9liorer la lisibilit\u00e9 avec JSON_PRETTY<\/a><\/p>\n

Par d\u00e9faut, l’affichage d’un document JSON dans MySQL ressemble \u00e0 ceci :<\/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
Tu peux avoir un affichage plus agr\u00e9able avec 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
Renvoie le nombre d’octets utilis\u00e9s pour stocker la repr\u00e9sentation binaire d’un document JSON avec\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
Dans cette collection, le document le plus lourd fait 916 octets, le plus l\u00e9ger 255 et la taille moyenne de tout les documents est 537,2814.<\/p>\n
Note<\/span>: C’est l’espace utilis\u00e9 pour stocker le document JSON tel qu’il a \u00e9t\u00e9 ins\u00e9r\u00e9 dans la colonne, avant toute mise \u00e0 jour partielle<\/a> qui aurait pu \u00eatre effectu\u00e9e par la suite.<\/em><\/p>\n
Fonctions qui recherchent des valeurs JSON<\/h2>\n
JSON_EXTRACT (->) \/ JSON_UNQUOTE \/ ->> operator<\/h3>\n
JSON_EXTRACT<\/a> (or -><\/a>) retourne des donn\u00e9es d’un document JSON.<\/p>\n
JSON_UNQUOTE<\/a>\u00a0supprime les guillemets des donn\u00e9es JSON et renvoie le r\u00e9sultat sous la forme d’une cha\u00eene de caract\u00e8res utf8mb4.<\/p>\n
->><\/a> l’op\u00e9rateur JSON \u00ab\u00a0unquote extract<\/em>\u00a0\u00bb qui est un raccourci pour 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
Les deux requ\u00eates ci-dessus sont similaires.<\/p>\n
Pour avoir le m\u00eame r\u00e9sultat sans les guillemets utilise ->><\/strong> ou 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
Les deux requ\u00eates ci-dessus sont similaires.<\/p>\n
JSON_CONTAINS<\/h3>\n
Recherche si la valeur de la cl\u00e9 correspond \u00e0 une valeur sp\u00e9cifi\u00e9e avec\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
Indique si un document JSON contient des donn\u00e9es dans l’un ou les chemins sp\u00e9cifi\u00e9s avec\u00a0JSON_CONTAINS_PATH<\/a>.<\/p>\n
Pour tester cette fonction, j’ins\u00e8re un document factice dans la collection restaurants :<\/p>\n
INSERT INTO restaurants (doc) VALUES ('{\"_id\": \"1234\", \"name\": \"Daz Restaurant\", \"cuisine\": \"West Indian\", \"restaurant_id\": \"4321\"}');<\/pre>\n
Combien y a t’il de documents sans note (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
Un seul !\u00a0 Tu peux alors facilement v\u00e9rifier la structure de ce 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
Un pont entre ces deux mod\u00e8les<\/h2>\n
Pour paraphraser\u00a0David Stokes<\/a> (MySQL Community Manager) dans son livre\u00a0MySQL 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
Faire de tels transformations avec MySQL est extr\u00eamement ais\u00e9 !<\/p>\n
Relationnel vers JSON<\/h3>\n
JSON_OBJECT<\/h4>\n
\u00c9value une liste de paires cl\u00e9\/valeur et renvoie un objet JSON contenant ces paires avec\u00a0JSON_OBJECT<\/a>.<\/p>\n
Une requ\u00eate SQL traditionnelle avec un jeu de r\u00e9sultats relationnel. En d’autres termes, le document JSON g\u00e9n\u00e8re des donn\u00e9es non-JSON :<\/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
Ce jeu de r\u00e9sultats peut \u00eatre converti au format JSON, plus pr\u00e9cis\u00e9ment en un objet JSON :<\/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
Autre exemple :<\/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
Prend deux noms de colonnes ou expressions et renvoie un objet JSON contenant des paires cl\u00e9\/valeur avec JSON_OBJECTAGG<\/a>.<\/p>\n
Agr\u00e9ger des colonnes est tr\u00e8s utile en SQL.<\/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