{"id":29111,"date":"2024-04-22T10:40:37","date_gmt":"2024-04-22T09:40:37","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/domina-el-almacenamiento-de-documentos-json-en-mysql\/"},"modified":"2024-06-03T18:44:02","modified_gmt":"2024-06-03T17:44:02","slug":"domina-el-almacenamiento-de-documentos-json-en-mysql","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/","title":{"rendered":"Master Storing JSON Documents in MySQL"},"content":{"rendered":"<p>The ability to handle data configured in complex and flexible formats is indispensable in modern web and mobile application development. MySQL, one of the most popular database management systems, has evolved to offer full support for handling data in JSON (JavaScript Object Notation) format. This support not only simplifies the storage of non-relational structured data, but also enables efficient integration between applications and the database.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/#%C2%BFPor_que_JSON_en_MySQL\" >Why JSON in MySQL?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/#Configuracion_Inicial\" >Initial setup<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/#Creando_una_Tabla_con_Columna_JSON\" >Creating a Table with JSON Column<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/#Insertando_Datos_en_Formato_JSON\" >Inserting Data in JSON Format<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/#Consultando_Datos_JSON\" >Querying JSON Data<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/#Modificando_Documentos_JSON\" >Modifying JSON Documents<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/#Ventajas_de_Usar_JSON_en_MySQL\" >Advantages of Using JSON in MySQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storing-json-documents-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFPor_que_JSON_en_MySQL\"><\/span>Why JSON in MySQL?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>JSON has established itself as the standard format for exchanging data on the web, thanks to its ease of use and its ability to structure data in a way that is both easily readable by humans and efficiently processable by machines. Using JSON in MySQL allows developers to directly store JSON documents, easily query and modify this data, and perform complex operations, such as aggregates and lookups, directly on the data structure.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Configuracion_Inicial\"><\/span>Initial setup<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To start working with JSON in MySQL, the first thing you need is to have MySQL installed on your machine. If you haven&#039;t done so yet, you can visit the <a href=\"https:\/\/www.mysql.com\/downloads\/\" rel=\"nofollow noopener\" target=\"_blank\">MySQL official page<\/a> to download the latest version.<\/p>\n<p>Once installed, you must ensure that your database is configured to correctly handle the special characters and encoding formats that JSON uses. To do this, it is recommended to use the UTF8MB4 character encoding, which is fully compatible with JSON.<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE DATABASE my_database DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Creando_una_Tabla_con_Columna_JSON\"><\/span>Creating a Table with JSON Column<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To store JSON documents, you need to define at least one column with the JSON data type in your MySQL table. Here I show you how you can create a table that includes a column of this type:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, json_data JSON, PRIMARY KEY (id) );<\/code><\/pre>\n<p>In this example, <code>json_data<\/code> is the column that will store our JSON documents. You could store a host of information related to a user in this column, from their contact information to their preferences and settings in your app.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Insertando_Datos_en_Formato_JSON\"><\/span>Inserting Data in JSON Format<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Inserting data into JSON column is as simple as inserting any other type of data in MySQL. Here is an example of how you can do it:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO users (json_data) VALUES (&#039;{&quot;name&quot;: &quot;John&quot;, &quot;age&quot;: 28, &quot;email&quot;: &quot;john@example.com&quot;}&#039;), (&#039;{&quot;name&quot;: &quot; Ana&quot;, &quot;age&quot;: 35, &quot;email&quot;: &quot;ana@example.com&quot;}&#039;);<\/code><\/pre>\n<p>This adds two new records to the table <code>users<\/code>, where each record contains a JSON document with the user&#039;s information.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Consultando_Datos_JSON\"><\/span>Querying JSON Data<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>One of the big advantages of using JSON in MySQL is the ability to run queries directly on the JSON document structure. For example, to get the name of all the users in your table, you could perform the following query:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT json_data-&gt;&gt;&quot;$.name&quot; AS name FROM users;<\/code><\/pre>\n<p>MySQL provides a variety of functions and operators to manipulate and query data in JSON format, giving you great flexibility in working with this type of data.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Modificando_Documentos_JSON\"><\/span>Modifying JSON Documents<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Modifying a JSON document stored in MySQL is also quite simple. Suppose you want to update John&#039;s age to 29. You can do this with the following query:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">UPDATE users SET json_data = JSON_SET(json_data, &#039;$.age&#039;, 29) WHERE JSON_EXTRACT(json_data, &#039;$.name&#039;) = &#039;John&#039;;<\/code><\/pre>\n<p>This operation uses the function <code>JSON_SET<\/code> to update the age directly in the JSON document.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ventajas_de_Usar_JSON_en_MySQL\"><\/span>Advantages of Using JSON in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Integrating JSON with MySQL not only allows you to store and query complex documents efficiently but also facilitates application scalability and integration with other technologies and frameworks, such as Node.js, Java and .NET, which have robust libraries. to handle data in JSON format.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>JSON support in MySQL transforms the way developers can work with flexible, structured data, enabling more agile development and a better user experience. If you are interested in going deeper into this topic or other advanced features of MySQL, feel free to visit my blog at <a href=\"https:\/\/nelkodev.com\/en\/\">nelkodev.com<\/a> or contact me directly through <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">nelkodev.com\/contact<\/a> if you have specific questions or need help with your project.<\/p>\n<p>By adopting JSON in MySQL, you not only optimize the storage and handling of data in your applications, but you also ensure that you stay up to date with current trends in software development.<\/p>","protected":false},"excerpt":{"rendered":"<p>The ability to handle data configured in complex and flexible formats is indispensable in modern web and mobile application development. MySQL, one of the most popular database management systems, has evolved to offer full support for handling data in JSON (JavaScript Object Notation) format. This support not only [\u2026]<\/p>","protected":false},"author":1,"featured_media":29112,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2033,2207],"tags":[1442,205,2189,639,1058,1473,1108,352,2208,2194],"class_list":["post-29111","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-mysql-data-types","category-nodejs","tag-almacenamiento","tag-blog","tag-curso","tag-data","tag-documentos","tag-domina","tag-json","tag-mysql","tag-nodejs","tag-types"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=29111"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29111\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29112"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}