{"id":29115,"date":"2024-04-25T05:33:21","date_gmt":"2024-04-25T04:33:21","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/domina-el-almacenamiento-con-json-en-mysql\/"},"modified":"2024-06-03T17:44:31","modified_gmt":"2024-06-03T16:44:31","slug":"domina-el-almacenamiento-con-json-en-mysql","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/master-storage-with-json-in-mysql\/","title":{"rendered":"Master Storage with JSON in MySQL"},"content":{"rendered":"<p>MySQL, the most popular relational database management system, has extended its classic functionalities to adapt to new industry demands. One of these innovations is support for JSON data. This format, known for its flexibility and ease of use in data exchange, can now be stored directly in MySQL. Next, we&#039;ll explore how you can use this powerful type of data to enrich your applications.<\/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-storage-with-json-in-mysql\/#Introduccion_al_Tipo_de_Dato_JSON_en_MySQL\" >Introduction to the JSON Data Type in MySQL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storage-with-json-in-mysql\/#%C2%BFPor_Que_Usar_JSON_en_MySQL\" >Why Use JSON in MySQL?<\/a><\/li><\/ul><\/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-storage-with-json-in-mysql\/#Como_Almacenar_Datos_JSON_en_MySQL\" >How to Store JSON Data in MySQL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storage-with-json-in-mysql\/#1_Crear_una_Tabla_con_una_Columna_JSON\" >1. Create a Table with a JSON Column<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storage-with-json-in-mysql\/#2_Insertar_Datos_en_Formato_JSON\" >2. Insert Data in JSON Format<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storage-with-json-in-mysql\/#3_Consultar_Datos_JSON\" >3. Query JSON Data<\/a><\/li><\/ul><\/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-storage-with-json-in-mysql\/#Funciones_Avanzadas_para_el_Manejo_de_JSON_en_MySQL\" >Advanced Functions for Handling 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-storage-with-json-in-mysql\/#Mejores_Practicas_y_Consideraciones\" >Best Practices and Considerations<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-storage-with-json-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Introduccion_al_Tipo_de_Dato_JSON_en_MySQL\"><\/span>Introduction to the JSON Data Type in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>JSON (JavaScript Object Notation) has become the de facto standard for exchanging information on the web and now integrates seamlessly into databases such as MySQL. Since version 5.7, MySQL offers native support for this type of data, allowing more efficient management of JSON structures directly from SQL.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"%C2%BFPor_Que_Usar_JSON_en_MySQL\"><\/span>Why Use JSON in MySQL?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The main advantages of using JSON in MySQL include:<\/p>\n<ul>\n<li><strong>Flexibility<\/strong>: Storing data in JSON format allows for a non-rigid data structure, ideal for data that may vary over time.<\/li>\n<li><strong>Simplicity<\/strong>: Facilitates the exchange of data between the frontend and the backend, by using a format already known and widely used in web development frameworks.<\/li>\n<li><strong>Efficiency<\/strong>- MySQL provides native functions to manipulate JSON data, making it fast and efficient for read and write operations.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Como_Almacenar_Datos_JSON_en_MySQL\"><\/span>How to Store JSON Data in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To start storing JSON documents in MySQL, you must follow the following steps:<\/p>\n<h3><span class=\"ez-toc-section\" id=\"1_Crear_una_Tabla_con_una_Columna_JSON\"><\/span>1. Create a Table with a JSON Column<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>First, you need to define a JSON type column in your table. Here is an example of how to create a table that includes a JSON column:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, JSON data );<\/code><\/pre>\n<p>In this example, the table <code>users<\/code> has a column <code>data<\/code> which is defined to store data in JSON format.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_Insertar_Datos_en_Formato_JSON\"><\/span>2. Insert Data in JSON Format<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Once the table is created, you can start inserting data in JSON format. For example:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO users (data) VALUES (&#039;{&quot;name&quot;: &quot;Ana&quot;, &quot;age&quot;: 25, &quot;hobbies&quot;: [&quot;soccer&quot;, &quot;painting&quot;]}&#039;);<\/code><\/pre>\n<p>This command inserts a JSON document that describes a user, including their name, age, and hobbies.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"3_Consultar_Datos_JSON\"><\/span>3. Query JSON Data<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>MySQL offers various functions to manipulate and query data stored in JSON format. For example, if you want to get the user&#039;s age:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT JSON_EXTRACT(data, &#039;$.age&#039;) AS age FROM users WHERE id = 1;<\/code><\/pre>\n<p>This query extracts the age directly from the JSON document stored in the column <code>data<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Funciones_Avanzadas_para_el_Manejo_de_JSON_en_MySQL\"><\/span>Advanced Functions for Handling JSON in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>MySQL not only allows you to store and retrieve JSON data, but also provides advanced functions for manipulating this data. Some of the most useful are:<\/p>\n<ul>\n<li><code>JSON_MODIFY<\/code>: Allows you to modify values within a JSON document.<\/li>\n<li><code>JSON_ARRAY_APPEND<\/code>: Adds an element to an array within a JSON.<\/li>\n<li><code>JSON_KEYS<\/code>: Returns the keys of a JSON object, useful for introspection.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Mejores_Practicas_y_Consideraciones\"><\/span>Best Practices and Considerations<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When working with JSON in MySQL, consider the following best practices:<\/p>\n<ol>\n<li><strong>Standardization<\/strong>: Although JSON allows for flexible structures, it is crucial to design the database in a way that maintains data integrity and efficiency.<\/li>\n<li><strong>Indices<\/strong>: MySQL allows JSON documents to be indexed, which is vital for improving query performance on large volumes of data.<\/li>\n<li><strong>Security<\/strong>: Make sure to validate and clean JSON data to avoid SQL injections and other attack vectors.<\/li>\n<\/ol>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>MySQL&#039;s ability to handle JSON data opens up a range of possibilities for developers, combining the robustness of a relational database system with the flexibility of non-relational document formats. Taking advantage of this functionality can mean a significant advance in how you manage and process data in your applications.<\/p>\n<p>Visit <a href=\"https:\/\/nelkodev.com\/en\/\">NelkoDev Blog<\/a> for more resources and guides. If you have questions or need personalized advice, do not hesitate to <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">contact me<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"<p>MySQL, the most popular relational database management system, has extended its classic functionalities to adapt to new industry demands. One of these innovations is support for JSON data. This format, known for its flexibility and ease of use in data exchange, can now be stored directly in a database.<\/p>","protected":false},"author":1,"featured_media":29116,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2033],"tags":[1442,205,90,2189,639,1473,1108,352,2194],"class_list":["post-29115","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-mysql-data-types","tag-almacenamiento","tag-blog","tag-con","tag-curso","tag-data","tag-domina","tag-json","tag-mysql","tag-types"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29115","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=29115"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29115\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29116"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29115"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29115"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29115"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}