recuperar - xml a tabla sql server

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

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

Si MySQL no necesita mirar dentro del 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 están dentro y tiene MariaDB 10.0.1 o MySQL 5.7 (con un tipo de datos y funciones JSON), entonces JSON podría ser 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 una búsqueda FULLTEXT o SPATIAL , JSON no es posible.
Para WHERE a=1 AND b=2 el INDEX(a,b) índice "compuesto" INDEX(a,b) es excelente; probablemente no pueda acercarse a JSON.
JSON funciona bien con datos "dispersos"; INDEXing funciona, pero no tan bien, con tal. (Me refiero a los valores que ''faltan'' o NULL para muchas de las filas).
JSON puede proporcionarle "matrices" y "árboles" sin recurrir a tablas adicionales. Pero profundice en tales matrices / árboles solo en la aplicación, no en SQL.
JSON es un mundo mejor que XML. (Mi opinión)
Si no desea ingresar a la cadena JSON excepto desde la aplicación, le recomiendo comprimirla (en el cliente) y almacenarla en un BLOB . Piense en ello como un .jpg: hay cosas allí, pero a SQL no le importa.

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

Cuando uso SQL o MySQL (o cualquier base de datos relacional), entiendo que guardar los datos en columnas regulares es mejor para indexar el bien y otros propósitos ...

El problema es que cargar y guardar datos JSON veces es mucho más simple. y facilita el desarrollo.

¿Hay alguna "regla de oro" para guardar datos JSON procesar en la base de datos?

¿Es una práctica absolutamente incorrecta hacerlo?

RESUMEN

Se dieron respuestas muy bonitas, pero sin duda la mejor organizada es la respuesta de @Shnugo que merece la recompensa.

También me gustaría señalar las respuestas dadas por @Gordon Linoff y @Amresh Pandey por explicar otros casos de uso especiales.

¡Gracias a Dios, y buen trabajo a todos!

El nuevo SQL Server proporciona funciones para procesar texto JSON. La información formateada como JSON se puede almacenar como texto en columnas estándar de SQL Server y SQL Server proporciona funciones que pueden recuperar valores de estos objetos JSON.

DROP TABLE IF EXISTS Person CREATE TABLE Person ( _id int identity constraint PK_JSON_ID primary key, value nvarchar(max) CONSTRAINT [Content should be formatted as JSON] CHECK ( ISJSON(value)>0 ) )

Esta estructura simple es similar a la colección estándar NoSQL que puede crear en las bases de datos NoSQL (por ejemplo, Azure DocumentDB o MongoDB) donde solo tiene una clave que representa la ID y el valor que representa JSON.

Tenga en cuenta que NVARCHAR no es solo un texto sin formato. SQL Server tiene un mecanismo de compresión de texto incorporado que puede comprimir de manera transparente los datos almacenados en el disco. La compresión depende del idioma y puede aumentar hasta un 50% según sus datos (consulte Compresión UNICODE).

La diferencia clave entre el servidor SQL y otras bases de datos NoSQL simples es que SQL Server le permite usar un modelo de datos híbrido donde puede almacenar varios objetos JSON en la misma "colección" y combinarlos con columnas relacionales regulares.

Como ejemplo, imagine que sabemos que cada persona en su colección tendrá FirstName y LastName, y que puede almacenar información general sobre la persona como un objeto JSON y números de teléfono / direcciones de correo electrónico como objetos separados. En SQL Server 2016 podemos crear fácilmente esta estructura sin ninguna sintaxis adicional:

DROP TABLE IF EXISTS Person CREATE TABLE Person ( PersonID int IDENTITY PRIMARY KEY, FirstName nvarchar(100) NOT NULL, LastName nvarchar(100) NOT NULL, AdditionalInfo nvarchar(max) NULL, PhoneNumbers nvarchar(max) NULL, EmailAddresses nvarchar(max) NULL CONSTRAINT [Email addresses must be formatted as JSON array] CHECK ( ISJSON(EmailAddresses)>0 ) )

En lugar de un solo objeto JSON, puede organizar sus datos en esta "colección". Si no desea verificar explícitamente la estructura de cada columna JSON, no necesita agregar la restricción de verificación JSON en cada columna (en este ejemplo, he agregado la restricción CHECK solo en la columna EmailAddresses).

Si compara esta estructura con la colección NoSQL estándar, puede notar que tendrá un acceso más rápido a los datos fuertemente tipados (Nombre y Apellido). Por lo tanto, esta solución es una buena opción para modelos híbridos donde puede identificar cierta información que se repite en todos los objetos, y otra información variable puede almacenarse como JSON. De esta manera, puede combinar flexibilidad y rendimiento.

Si compara esta estructura con el esquema de la base de datos AdventureWorks de la tabla Persona, puede notar que hemos eliminado muchas tablas relacionadas.

Además de la simplicidad del esquema, sus operaciones de acceso a datos serán más simples en comparación con la compleja estructura relacional. Ahora puede leer una sola tabla en lugar de unir varias tablas. Cuando necesite insertar una nueva persona con información relacionada (direcciones de correo electrónico, números de teléfono), puede insertar un solo registro en una tabla en lugar de insertar un registro en la tabla Persona de AdventureWorks, tomando la columna de identidad para encontrar la clave externa que se usará para almacenar teléfonos , direcciones de correo electrónico, etc. Además, en este modelo puede eliminar fácilmente la fila de una sola persona sin eliminaciones en cascada utilizando relaciones de clave externa.

Las bases de datos NoSQL están optimizadas para operaciones simples de lectura, inserción y eliminación: SQL Server 2016 le permite aplicar la misma lógica en la base de datos relacional.

Restricciones de JSON En los ejemplos anteriores, hemos visto cómo agregar una restricción simple que valida que el texto almacenado en la columna tenga el formato correcto. Aunque JSON no tiene un esquema fuerte, también puede agregar restricciones complejas combinando funciones que leen valores de JSON y funciones T-SQL estándar:

ALTER TABLE Person ADD CONSTRAINT [Age should be number] CHECK ( ISNUMERIC(JSON_VALUE(value, ''$.age''))>0 ) ALTER TABLE Person ADD CONSTRAINT [Person should have skills] CHECK ( JSON_QUERY(value, ''$.skills'') IS NOT NULL) First constraint will take the value of $.age property and check is this numeric value. Second constraint will try to find JSON object in $.skills property and verify that it exists. The following INSERT statements will fail due to the violation of constraints: INSERT INTO Person(value) VALUES (''{"age": "not a number", "skills":[]}'') INSERT INTO Person(value) VALUES (''{"age": 35}'')

Tenga en cuenta que las restricciones CHECK pueden ralentizar sus procesos de inserción / actualización, por lo que puede evitarlas si necesita un rendimiento de escritura más rápido.

Almacenamiento JSON comprimido Si tiene texto JSON grande, puede comprimir explícitamente el texto JSON utilizando la función COMPRESS incorporada. En el siguiente ejemplo, el contenido comprimido de JSON se almacena como datos binarios, y hemos calculado una columna que descomprime JSON como texto original usando la función DECOMPRESS:

CREATE TABLE Person ( _id int identity constraint PK_JSON_ID primary key, data varbinary(max), value AS CAST(DECOMPRESS(data) AS nvarchar(max)) ) INSERT INTO Person(data) VALUES (COMPRESS(@json))

Las funciones COMPRESS y DECOMPRESS usan compresión GZip estándar. Si su cliente puede manejar la compresión GZip (por ejemplo, un navegador que comprende el contenido gzip), puede devolver directamente el contenido comprimido. Tenga en cuenta que esto es una compensación de rendimiento / almacenamiento. Si consulta con frecuencia datos comprimidos, migra tiene un rendimiento más lento porque el texto debe descomprimirse cada vez.

Nota: Las funciones JSON solo están disponibles en SQL Server 2016+ y Azure SQL Database.

Se puede leer más en la fuente de este artículo.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/

Esto es demasiado largo para un comentario.

Si estuviera "absolutamente equivocado", entonces la mayoría de las bases de datos no lo admitirían. De acuerdo, la mayoría de las bases de datos admiten comas en la cláusula FROM 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 cada registro.

Otro caso es el caso 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 los registros sobre los que desea consultar, entonces es mejor que los coloque en las columnas adecuadas de la base de datos. Sin embargo, los datos son complicados y hay un lugar para formatos como JSON.

La "regla de oro" que uso, de forma manual, es que si necesito JSON en su formato sin procesar, está bien almacenarlo. Si tengo que hacer un punto especial al analizarlo, entonces no lo es.

Por ejemplo, si estoy creando una API que envía JSON sin formato, y por alguna razón este valor no va a cambiar, entonces está bien almacenarlo como JSON sin formato. Si tengo que analizarlo, cambiarlo, actualizarlo, etc., entonces no tanto.

La pregunta que debes hacer es:

¿Estoy atado a usar solo esta base de datos?

HACER

Si puede usar una base de datos diferente para almacenar JSON, use una solución de almacenamiento de documentos como CouchDB, DynamoDB o MongoDB.
Utilice la capacidad de estos DB de almacenamiento de documentos para indexar y buscar datos jerárquicos.
Use una base de datos relacional para sus datos relacionales.
Use una base de datos relacional para informes, almacenamiento de datos y minería de datos.

Almacene JSON como cadena si es posible.
Intente y obtenga la longitud máxima de datos JSON.
Use varchar para almacenar JSON (use text / blob si es necesario).
Intenta buscar valores en el JSON almacenado.
Preocuparse por escapar de JSON para almacenar como cadena.

Las preguntas principales son

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

JSON (como XML) es ideal para el intercambio de datos, el almacenamiento pequeño y las estructuras genéricamente definidas, pero no puede participar en 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 volver 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ñalas esto y lloras: ¡ Cambia eso inmediatamente!

¿Qué pasa con XML o JSON? ¿Están estos tipos rompiendo 1.NF? Bueno, si y no ...

Está perfectamente bien almacenar una estructura completa como un bit de información si en realidad es un bit de información . ¿Obtiene una respuesta SOAP y desea almacenarla porque podría necesitarla para referencia futura (pero no utilizará estos datos para sus propios procesos )? ¡Solo guárdalo como está !

Ahora imagine una 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 diseñadas adecuadamente 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. Desea conservar los datos de la persona durante un momento determinado. ¿Algunos días después la persona le 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 una porción única , está bien ...
Pero si necesita las partes internas regularmente o si esto significaría un almacenamiento duplicado redundante, no está bien ...

Almacenamiento físico

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 es 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 ocurrir más tarde, pero por ahora supongo que JSON no será tan eficiente como 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 cadenas ocultas ...

¿Qué significa esto para ti?

su adorable artista de DB :-D sabe que almacenar JSON tal cual está en contra de los principios comunes de los RDBM. Él sabe,

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

Hay algunas soluciones (dependiendo del RDBMS que esté usando), pero la mayoría de ellas no funcionan de la manera que le gustaría ...

La respuesta a tu pregunta en resumen

SÍ

Si no desea utilizar datos, que se almacenan en su JSON para operaciones costosas (filtro / unión / clasificación).
Puede almacenar esto como cualquier otro contenido solo existe . Estamos almacenando muchas imágenes como BLOB, pero no intentaríamos filtrar todas las imágenes con una flor ...
Si no te molestas en absoluto lo que hay dentro (solo guárdalo y léelo como un poco de información)
Si las estructuras son variables, lo que harí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 elevado.

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

Puede comenzar con el 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 que esto podría ser mañana :-D

ACTUALIZAR

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

ACTUALIZACIÓN 2: Más información 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 que consultar un XML en SQL-Server.

Algunas reflexiones al respecto:

Algunas verificaciones cruzadas con el "experimento":

El "experimento" mide mucho, pero no el rendimiento de XML vs. JSON . Hacer la misma acción contra la misma cadena (sin cambios) repetidamente no es un escenario realista
¡Los ejemplos probados son demasiado simples para una declaración general !
El valor leído es siempre el mismo y ni siquiera se usa. El optimizador verá esto ...
¡Ni una sola palabra sobre el poderoso soporte de XQuery ! ¿Encontrar un producto con una ID dada dentro de una matriz? JSON necesita leer todo el lote y luego usar un filtro usando WHERE , mientras que XML permitiría un XQuery predicate interno. No hablar sobre FLWOR ...
aparece el código de "experimentos" tal como está en mi sistema: JSON parece ser 3 veces más rápido (pero no 10 veces).
Agregar /text() a XPath reduce esto a menos de 2x . En el artículo relacionado, el usuario "Mister Magoo" ya señaló esto, pero el título de cebo de clic todavía no ha cambiado ...
Con un JSON tan fácil como el dado en el "experimento", el enfoque T-SQL puro más rápido fue una combinación de SUBSTRING y CHARINDEX :-D

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

Usando un JSON y un XML idéntico con más de un Product (una matriz JSON vs. nodos hermanos)
JSON y XML están cambiando ligeramente (10000 números en ejecución) y se insertan en las tablas.
Hay una llamada inicial contra ambas tablas para evitar el sesgo de la primera llamada
Todas las 10000 entradas se leen y los valores recuperados se insertan en otra tabla.
El uso de GO 10 se ejecutará en este bloque diez veces para evitar el sesgo de la primera llamada

El resultado final muestra claramente que JSON es más lento que XML (no tanto, aproximadamente 1.5x en un ejemplo todavía 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 una acción de cadena pura , mientras que XML se analiza y transforma. Esto es bastante costoso en la primera acción, pero acelerará todo, una vez hecho esto.
JSON podría ser mejor en una acción de una sola vez (evita la sobrecarga de crear una representación jerárquica interna de un XML)
Con un ejemplo todavía muy simple pero más realista, XML será más rápido en lectura simple
Siempre que sea necesario leer un elemento específico de una matriz, para filtrar todas las entradas donde se incluye un ProductID dado en la matriz, o para navegar hacia arriba y hacia abajo por la ruta, JSON no puede aguantar. Debe analizarse completamente de una cadena, cada vez que tenga que agarrarlo ...

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

Los de Json no son geniales en los db relacionales. Si despliega el json en columnas y lo almacena en un db, es genial, pero almacenar un json como un blob está al lado de usarlo como sistema de archivo de datos.

Podría haber varias razones para no desplegar un json y almacenarlo en una sola columna, pero la decisión se habría tomado ya que los valores en ese campo json no se utilizarían para ninguna consulta (o los valores ya se han desplegado en columnas).

Además, la mayor parte del procesamiento de json si se consultara el campo estaría fuera del entorno sql, ya que sql no está destinado al procesamiento json. La verdadera pregunta es: ¿dónde almaceno este json? ¿Debo dejarlo como archivos planos y, cuando sea necesario, consultarlos a través de algún otro sistema (spark / hive / etc).

Estoy de acuerdo con su artista de DB, no use RDBMS para el archivo. Hay opciones más baratas. También los blobs json pueden volverse enormes y pueden comenzar a empantanar el espacio del disco de la base de datos con el tiempo.

PostgreSQL tiene un tipo de datos json y jsonb

Estos son algunos ejemplos:

CREATE TABLE orders ( ID serial NOT NULL PRIMARY KEY, info json NOT NULL ); INSERT INTO orders (info) VALUES ( ''{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'' ), ( ''{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'' ), ( ''{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'' );

PostgreSQL proporciona dos operadores nativos -> y ->> para consultar datos JSON.

El operador -> devuelve el campo de objeto JSON por clave.

El operador ->> devuelve el campo de objeto JSON por texto.

SELECT info -> ''customer'' AS customer FROM orders; SELECT info ->> ''customer'' AS customer FROM orders WHERE info -> ''items'' ->> ''product'' = ''Diaper''