¿Cuándo puedo guardar datos JSON o XML en una tabla SQL?

Solución:

Las principales preguntas son

• ¿Qué vas a hacer con estos datos? y
• ¿Cómo estás filtrando / clasificando / uniendo / manipulando estos datos?

JSON (como XML) es ideal para el intercambio de datos, el almacenamiento pequeño y las estructuras definidas genéricamente, pero no puede participar en las acciones típicas que ejecuta dentro de su RDBMS. En la mayoría de los casos, será mejor transferir sus datos JSON a tablas normales y vuelva a crear el JSON cuando lo necesite.

XML / JSON y 1.NF

La primera regla de normalización dicta, nunca almacenar más de un bit de información en una columna. ¿Ves una columna “PersonName” con un valor como “Mickey Mouse”? Señala esto y llora: ¡Cambia eso de inmediato!

¿Qué pasa con XML o JSON? ¿Estos tipos están rompiendo 1.NF? Bueno, sí y no …

Está perfectamente bien almacenar una estructura completa. como un poco de información si esto es un poco de información Realmente. Obtiene una respuesta SOAP y desea almacenarla porque es posible que la necesite para referencia futura (pero la necesitará no utilice estos datos para sus propios procesos)? Solo guárdalo como es!

Ahora imagina un estructura compleja (XML o JSON) que representa a una persona (con su dirección, más detalles …). Ahora pones esto en una columna como PersonInCharge. ¿Esto esta mal? ¿No debería esto vivir en tablas relacionadas correctamente diseñadas con una referencia de clave externa en lugar de XML / JSON? Especialmente si la misma persona puede aparecer en muchas filas diferentes, definitivamente es incorrecto usar un enfoque XML / JSON.

Pero ahora imagine la necesidad de almacenar datos históricos. Tú quieres persistir los datos de la persona para un momento dado. ¿Unos días después la persona te dice una nueva dirección? ¡No hay problema! La dirección anterior vive en un XML / JSON si alguna vez la necesita …

Conclusión: Si almacena los datos solo para conservarlos, está bien. Si estos datos son único porción, está bien …
Pero si necesitas el partes internas regularmente o si esto significa un almacenamiento duplicado redundante, no está bien …

Almacenamiento fisico

Lo siguiente es para SQL Server y puede ser diferente en otros RDBM.

XML no se almacena como el texto que ve, sino como un árbol de jerarquía. ¡Consultar esto tiene un rendimiento asombrosamente bueno! ¡Esta estructura no se analiza a nivel de cadena!
JSON en SQL Server (2016+) vive en una cadena y debe analizarse. No hay un tipo JSON nativo real (como si hubiera un tipo XML nativo). Esto podría llegar más tarde, pero por ahora supongo que JSON no tendrá el mismo rendimiento que XML en SQL Server (consulte la sección ACTUALIZACIÓN 2). Cualquier necesidad de leer un valor de JSON necesitará una gran cantidad de llamadas a métodos de cadena ocultos …

¿Qué significa esto para ti?

tu adorable artista de DB 😀 sabe, que almacenar JSON como es, va en contra de los principios comunes de los RDBM. Él sabe,

• que un JSON probablemente está rompiendo 1.NF
• que un JSON puede cambiar con el tiempo (misma columna, contenido diferente).
• que un JSON no es fácil de leer y es muy difícil filtrar / buscar / unirse u ordenar por él.
• que tales operaciones trasladarán bastante carga extra a un servidor de base de datos pequeño y pobre

Hay algunas soluciones alternativas (dependiendo del RDBMS que esté utilizando), pero la mayoría de ellas no funcionan como le gustaría …

La respuesta a tu pregunta en breve

SÍ

• Si tu no quiero usar datos, que se almacenan dentro de tu JSON para operaciones costosas (filtrar / unir / ordenar).
  Puede almacenar esto como cualquier otro solo existe contenido. Estamos almacenando muchas imágenes como BLOB, pero no intentaríamos filtrar todas las imágenes con una flor …
• Si no se molesta en absoluto en lo que hay dentro (solo guárdelo y léalo como un bit de información)
• Si las estructuras son variables, sería más difícil crear tablas físicas que trabajar con datos JSON.
• Si la estructura está profundamente anidada, el almacenamiento en tablas físicas es demasiado alto.

NO

• Si desea usar los datos internos como usaría los datos de una tabla relacional (filtro, índices, uniones …)
• Si almacenaría duplicados (crear redundancia)
• En general: si tiene problemas de rendimiento (¡seguro que los enfrentará en muchos escenarios típicos!)

Puede comenzar con JSON dentro de una columna de cadena o como BLOB y cambiar esto a tablas físicas cuando lo necesite. Mi bola de cristal mágica me dice, esto podría ser mañana 😀

ACTUALIZAR

Encuentre algunas ideas sobre rendimiento y espacio en disco aquí: https://stackoverflow.com/a/47408528/5089204

ACTUALIZACIÓN 2: Más sobre el rendimiento …

Las siguientes direcciones son compatibles con JSON y XML en SQL-Server 2016

El usuario @ mike123 señaló un artículo en un blog oficial de microsoft que parece probar en un experimento que consultar un JSON es 10 veces más rápido luego consultar un XML en SQL-Server.

Algunas reflexiones sobre eso:

Algunas verificaciones cruzadas con el “experimento”:

• los El “experimento” mide mucho, pero no el rendimiento de XML frente a JSON.. Hacer la misma acción contra la misma cadena (sin cambios) repetidamente no es un escenario realista
• Los ejemplos probados son De lejos a simple para una declaración general.!
• El valor leído es siempre el mismo y ni siquiera se utiliza. El optimizador verá esto …
• Ni una sola palabra sobre los poderosos XQuery ¡apoyo! ¿Encontrar un producto con un ID determinado dentro de una matriz? JSON necesita leer todo y usar un filtro luego usando WHERE, tiempo XML permitiría un interno XQuery predicate. Por no hablar de FLWOR…
• el código de “experimentos” como es en mi sistema aparece: JSON parece ser 3 veces más rápido (pero no 10 veces).
• Añadiendo /text() al XPath reduce esto a menos de 2x. En el artículo relacionado, el usuario “Mister Magoo” ya señaló esto, pero el cebo de clic el título aún no ha cambiado …
• Con un JSON tan sencillo como el que se proporciona en el “experimento”, el enfoque de T-SQL puro más rápido fue una combinación de SUBSTRING y CHARINDEX 😀

El siguiente código mostrará un experimento más realista.

• Usando un JSON y un XML idéntico con más de uno Product (una matriz JSON frente a nodos hermanos)
• JSON y XML están cambiando ligeramente (10000 números en ejecución) y se insertan en tablas.
• Hay una llamada inicial contra ambas tablas para evitar sesgo de primera llamada
• Se leen las 10000 entradas y los valores recuperados se insertan en otra tabla.
• Utilizando GO 10 correrá a través de este bloque diez veces para evitar sesgo de primera llamada

El resultado final muestra claramente que JSON es más lento que XML (no tanto, alrededor de 1.5x en un ejemplo muy simple).

La declaración final:

• Con un ejemplo demasiado simplificado en circunstancias indebidas, JSON puede ser más rápido que XML
• Tratar con JSON es acción de cuerda pura, mientras que XML se analiza y transforma. Esto es bastante caro en la primera acción, pero acelerará todo, una vez hecho esto.
• JSON podría ser mejor en un una vez acción (evita la sobrecarga de crear una representación jerárquica interna de un XML)
• Con un ejemplo aún muy simple pero más realista, XML será más rápido en una lectura simple
• Siempre que sea necesario leer un elemento específico de una matriz, filtrar todas las entradas donde se incluye un ProductID determinado en la matriz, o navegar hacia arriba y hacia abajo en la ruta, JSON no puede mantenerse. Debe analizarse completamente de una cadena, cada vez que tenga que agarrarla …

El código de prueba

USE master;
GO
--create a clean database
CREATE DATABASE TestJsonXml;
GO
USE TestJsonXml;
GO
--create tables
CREATE TABLE TestTbl1(ID INT IDENTITY,SomeXml XML);
CREATE TABLE TestTbl2(ID INT IDENTITY,SomeJson NVARCHAR(MAX));
CREATE TABLE Target1(SomeString NVARCHAR(MAX));
CREATE TABLE Target2(SomeString NVARCHAR(MAX));
CREATE TABLE Times(Test VARCHAR(10),Diff INT)
GO
--insert 10000 XMLs into TestTbl1
WITH Tally AS(SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*2 AS Nmbr FROM master..spt_values AS v1 CROSS APPLY master..spt_values AS v2)
INSERT INTO TestTbl1(SomeXml)
SELECT 
N'<Root>
    <Products>
    <ProductDescription>
        <Features>
            <Maintenance>' + CAST(Nmbr AS NVARCHAR(10)) + ' year parts and labor extended maintenance is available</Maintenance>
            <Warranty>1 year parts and labor</Warranty>
        </Features>
        <ProductID>' + CAST(Nmbr AS NVARCHAR(10)) + '</ProductID>
        <ProductName>Road Bike</ProductName>
    </ProductDescription>
    <ProductDescription>
        <Features>
            <Maintenance>' + CAST(Nmbr + 1 AS NVARCHAR(10)) + ' blah</Maintenance>
            <Warranty>1 year parts and labor</Warranty>
        </Features>
        <ProductID>' + CAST(Nmbr + 1 AS NVARCHAR(10)) + '</ProductID>
        <ProductName>Cross Bike</ProductName>
    </ProductDescription>
    </Products>
</Root>'
FROM Tally;

--insert 10000 JSONs into TestTbl2
WITH Tally AS(SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr FROM master..spt_values AS v1 CROSS APPLY master..spt_values AS v2)
INSERT INTO TestTbl2(SomeJson)
SELECT 
N'{
    "Root": {
        "Products": {
            "ProductDescription": [
                {
                    "Features": {
                        "Maintenance": "' + CAST(Nmbr AS NVARCHAR(10)) + ' year parts and labor extended maintenance is available",
                        "Warranty": "1 year parts and labor"
                    },
                    "ProductID": "' + CAST(Nmbr AS NVARCHAR(10)) + '",
                    "ProductName": "Road Bike"
                },
                {
                    "Features": {
                        "Maintenance": "' + CAST(Nmbr + 1 AS NVARCHAR(10)) + ' blah",
                        "Warranty": "1 year parts and labor"
                    },
                    "ProductID": "' + CAST(Nmbr + 1 AS NVARCHAR(10)) + '",
                    "ProductName": "Cross Bike"
                }
            ]
        }
    }
}'
FROM Tally;
GO

--Do some initial action to avoid first-call-bias
INSERT INTO Target1(SomeString)
SELECT SomeXml.value('(/Root/Products/ProductDescription/Features/Maintenance/text())[1]', 'nvarchar(4000)')
FROM TestTbl1;
INSERT INTO Target2(SomeString)
SELECT JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[0].Features.Maintenance')
FROM TestTbl2;
GO

--Start the test
DECLARE @StartDt DATETIME2(7), @EndXml DATETIME2(7), @EndJson DATETIME2(7);

--Read all ProductNames of the second product and insert them to Target1
SET @StartDt = SYSDATETIME();
INSERT INTO Target1(SomeString)
SELECT SomeXml.value('(/Root/Products/ProductDescription/ProductName/text())[2]', 'nvarchar(4000)')
FROM TestTbl1
ORDER BY NEWID();
--remember the time spent
INSERT INTO Times(Test,Diff)
SELECT 'xml',DATEDIFF(millisecond,@StartDt,SYSDATETIME());

--Same with JSON into Target2
SET @StartDt = SYSDATETIME();
INSERT INTO Target2(SomeString)
SELECT JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[1].ProductName')
FROM TestTbl2
ORDER BY NEWID();
--remember the time spent
INSERT INTO Times(Test,Diff)
SELECT 'json',DATEDIFF(millisecond,@StartDt,SYSDATETIME());

GO 10 --do the block above 10 times

--Show the result
SELECT Test,SUM(Diff) AS SumTime, COUNT(Diff) AS CountTime
FROM Times
GROUP BY Test;
GO
--clean up
USE master;
GO
DROP DATABASE TestJsonXml;
GO

El resultado (SQL Server 2016 Express en un Acer Aspire v17 Nitro Intel i7, 8GB Ram)

Test    SumTime 
------------------
json    2706    
xml     1604    

Esto es demasiado para un comentario.

Si fuera “absolutamente incorrecto”, la mayoría de las bases de datos no lo admitirían. Bien, la mayoría de las bases de datos admiten comas en el FROM cláusula y lo veo como “absolutamente incorrecto”. Pero el soporte para JSON es un nuevo desarrollo, no una “característica” compatible con versiones anteriores.

Un caso obvio es cuando la estructura JSON es simplemente un BLOB que se devuelve a la aplicación. Entonces no hay debate, aparte de la sobrecarga de almacenar JSON, que es innecesariamente detallado para datos estructurados con campos comunes en todos los registros.

Otro caso es el de columnas “dispersas”. Tiene filas con muchas columnas posibles, pero estas varían de una fila a otra.

Otro caso es cuando desea almacenar registros “anidados” en un registro. JSON es poderoso.

Si el JSON tiene campos comunes en todos los registros sobre los que desea realizar consultas, por lo general es mejor colocarlos en las columnas de la base de datos adecuadas. Sin embargo, los datos son complicados y hay lugar para formatos como JSON.

Agitaré mi varita mágica. ¡Maricón! Reglas de oro sobre el uso de JSON:

• Si MySQL no necesita buscar dentro JSON, y la aplicación simplemente necesita una colección de cosas, entonces JSON está bien, posiblemente incluso mejor.

• Si va a buscar datos que se encuentran dentro y tiene MariaDB 10.0.1 o MySQL 5.7 (con un tipo de datos y funciones JSON), luego JSON podría Sé práctico. Las columnas “Dinámicas” de MariaDB 5.3 son una variante de esto.

• Si está haciendo cosas de “Entidad-Atributo-Valor”, entonces JSON no es bueno, pero es el menor de varios males. http://mysql.rjweb.org/doc.php/eav

• Para buscar por una columna indexada, no tener el valor enterrado dentro de JSON es una gran ventaja.

• Para buscar por un rango en una columna indexada, o un FULLTEXT buscar o SPATIAL, JSON no es posible.

• Para WHERE a=1 AND b=2 el índice “compuesto” INDEX(a,b) es genial; probablemente no pueda acercarse a JSON.

• JSON funciona bien con datos “dispersos”; INDEXing funciona, pero no tan bien, con tales. (Me refiero a valores que ‘faltan’ o NULL para muchas de las filas).

• JSON puede proporcionarle “matrices” y “árboles” sin tener que recurrir a tablas adicionales. Pero profundice en tales matrices / árboles solamente en la aplicación no en SQL.

• JSON es mucho mejor que XML. (Mi opinión)

• Si no desea ingresar a la cadena JSON excepto desde la aplicación, le recomiendo comprimirlo (en el cliente) y almacenarlo en un BLOB. Piense en ello como un .jpg: hay cosas ahí, pero a SQL no le importa.

Indique su solicitud; tal vez podamos ser más específicos.

---

---

Utiliza Nuestro Buscador