JSON en SQL Server 2016: Página 3 de 4 Páginas

Transformar texto JSON a tabla relacional – OPENJSON

OPENJSON es una función del valor de tabla (TVF) que se ve en texto JSON, localiza una gran variedad de objetos JSON, recorre en iteración los elementos de la matriz y cada elemento devuelve una fila en el resultado de salida.

En el ejemplo anterior, podemos especificar dónde ubicar la matriz JSON que debe ser abierta (es decir, en el $. Ruta de órdenes), qué columna debe ser devuelto como resultado, y donde en el JSON pueden ubicarse los valores de los objetos que se devolverá como células. 
OPENJSON se puede utilizar en cualquier consulta que trabaja con datos. Como ejemplo, podemos transformar un conjunto de filas de una matriz JSON en la variable @orders e insertarlos en una tabla estándar:

INSERT INTO Orders(Number, Date, Customer, Quantity)
SELECT Number, Date, Customer, Quantity
 OPENJSON (@orders)
 WITH (
        Number varchar(200), 
        Date datetime,
        Customer varchar(200),
        Quantity int
 ) AS OrdersArray

Cuatro columnas en el conjunto de resultados devuelto por OPENJSON se definen en la cláusula WITH. OPENJSON tratará de encontrar las propiedades de número, fecha, cliente y la cantidad en cada objeto JSON y convertir sus valores en columnas en el resultado. Por defecto, es NULL devuelve si no se encuentra la propiedad. La Asunción en la consulta anterior es que la variable @orders contiene la siguiente matriz JSON:

'[
   {"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200},
   {"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100},
   {"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250},
   {"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200}
]'

Como se puede ver, la transformación de un texto JSON a una forma relacional es simple. Solo tienes que especificar nombres de columna y tipos y OPENJSON encontrará propiedades en JSON que coinciden con estas columnas. En este ejemplo, se utiliza JSON simple; sin embargo, OPENJSON puede manejar cualquier estructura anidada/jerárquica de objetos JSON. 
, OPENJSON puede utilizarse para combinar relacional y datos JSON en la misma consulta. Si suponemos que la matriz JSON que se muestra en el ejemplo anterior se almacena en la columna de pedidos, la consulta siguiente puede combinar las columnas y los campos JSON:

SELECT Id, FirstName, LastName, Number, Date, Customer, Quantity
 FROM Person
    CROSS APPLY OPENJSON (OrdersJson)
                            WITH (
                                        Number varchar(200), 
                                        Date datetime,
                                        Customer varchar(200),
                                        Quantity int ) AS OrdersArray

OPENJSON abre una matriz de cada celda y devuelve una fila por cada objeto JSON (es decir, elemento) en la matriz. Cruz OPENJSON aplicar sintaxis se utilizan para "juntar" filas en la tabla con la tabla interna del niño que se materializará de una matriz JSON en la celda JSON.

Indexación de datos JSON

Aunque los valores en JSON formato como texto, puede indexarlos al igual que cualquier otros valores en columnas de la tabla. Puede utilizar Cluster no estándar o índices de búsqueda de texto completo. 
Si desea crear un índice en alguna propiedad JSON que se utiliza con frecuencia en las consultas, puede crear una columna calculada no persistió que hace referencia el valor y se crea un índice estándar en esa columna. En el ejemplo siguiente, optimizamos consultas que filtran filas mediante la propiedad de .Company $ en la columna de InfoJSON:

ALTER TABLE Person
ADD vCompany AS JSON_VALUE(InfoJSON, '$.Company')

CREATE INDEX idx_Person_1
    ON Person(vCompany)

Como se puede ver, SQL Server proporciona un modelo híbrido, donde usted puede poner valores de JSON en columnas de clave o incluye y utiliza valores JSON y columnas estándar en el mismo índice. 
Puesto que JSON es texto normal, usted puede utilizar el índice de texto completo. Se pueden crear índices de texto completo en matrices de valores. 

Crear un índice de texto completo en una columna que contiene una matriz JSON, o puede crear una columna calculada que hace referencia a un array en la columna existente y crear un índice de búsqueda de texto completo en la columna:

ALTER TABLE Person
ADD vEmailAddresses AS JSON_QUERY(InfoJSON, '$.Contact.Emails')

CREATE FULLTEXT INDEX ON Person(vEmailAddresses)
    KEY INDEX PK_Person_ID ON jsonFullTextCatalog;

Índice de texto completo es útil si usted necesita para optimizar las consultas que intentan encontrar filas en la matriz JSON contiene algún valor:

SELECT PersonID, FirstName,LastName,vEmailAddresses
FROM Person
WHERE CONTAINS(vEmailAddresses, 'john@mail.microsoft.com')

Esta consulta devolverá filas de persona donde la matriz de correo electrónico contiene el valor 'john@mail.microsoft.com'. Índice de texto completo no tiene ninguna regla especial para el análisis de JSON. Dividirá un array JSON usando separadores (es decir doble comillas, comas, soportes) y los valores del índice en un array. Índice de texto completo es aplicable en matrices de números y valores de cadena simple. Si tienes objetos más complejos de una matriz JSON, un índice de texto completo no se puede aplicar directamente porque el sistema no sabe la diferencia entre claves y valores. 

Como se puede ver, los mismos métodos de indexación se utilizan tanto en columnas relacionales y valores JSON.

JSON en SQL Server 2016: Página 1 de 4

JSON en SQL Server 2016: Página 2 de 4

JSON en SQL Server 2016: Página 4 de 4

Read More

JSON en SQL Server 2016: Página 2 de 4 Páginas

Almacenamiento de datos de JSON en SQL Server:

Desde sistemas externos formato información como texto JSON, JSON también se almacena en SQL Server como texto. Puede utilizar columnas NVARCHAR estándar para almacenar datos JSON. Una simple mesa donde algunos datos como JSON se muestran en el ejemplo siguiente:

CREATE TABLE Person (
 Id int IDENTITY PRIMARY KEY NONCLUSTERED,
 FirstName nvarchar(100) NOT NULL, 
 LastName nvarchar(100) NOT NULL,

 InfoJson nvarchar(max)

) WITH (MEMORY_OPTIMIZED=ON)

Aquí puede ver la principal diferencia entre los sistemas relacionales y documento tradicionales y el modelo híbrido que proporciona SQL Server 2016. En el año 2016 de SQL Server, puede combinar ambas columnas escalares (FirstName y LastName en este ejemplo) y las columnas que contienen JSON (InfoJSON en este ejemplo).

En SQL Server, puede organizar los datos como una o varias columnas JSON o escalar y combinarlos con columnas espacial o XML si es necesario. En la misma tabla, se pueden combinar columnas relacionales estándar que permiten un acceso más rápido y JSON que proporcionan flexibilidad y desarrollo rápido de aplicaciones. A diferencia de almacenamiento sólo relacional o sólo documento donde tienes que elegir entre dos principios de modelado de datos, SQL Server le ofrece un modelo de almacenamiento de datos híbrido donde se puede usar lo mejor de ambos métodos de modelado de datos.

Aunque JSON se almacena en una columna de texto, no es sólo "un texto sin formato." SQL Server tiene soporte incorporado para optimizar el almacenamiento de información de columnas de texto mediante diversos mecanismos de compresión tales como compresión UNICODE que puede proporcionar hasta una 50% de compresión. También puede almacenar texto JSON en tienda tablas de columna o comprimirlo explícitamente usando la función incorporada de COMPRESS que utiliza el algoritmo de GZip.

JSON es totalmente compatible con cualquier componente de SQL Server o la tecnología que funciona con datos NVARCHAR. En el ejemplo anterior, JSON se almacena en una tabla en memoria OLTP (Hekaton) que proporciona rendimiento de procesamiento extrema. Puede almacenar JSON en tablas estándar, índices de almacén de columnas, o en FILESTREAM. También se puede cargar de Hadoop Polybase tablas externas, leer desde sistemas de archivos, estirar a SQL Azure, utilice cualquier método de replicación y mucho más. Si combinas las tablas que almacenan documentos JSON con otras características de SQL Server, como Temporal o seguridad de nivel de fila, es posible que algunas características de gran alcance que no están disponibles en las bases de datos existentes documento. 

Si no quieres mantener JSON como formato de texto libre, puede Agregar una validación que comprueba que JSON en la columna de texto está formateado correctamente utilizando restricciones CHECK estándar y la función ISJSON:

ALTER TABLE Person
ADD CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON( InfoJSON )> 0 )

Se trata de una restricción de control de SQL Server estándar que permite validar si el texto almacenado en la columna JSON está correctamente formateado. Esta restricción es opcional, usted puede dejar una columna de texto plano como en el ejemplo anterior; sin embargo, las consultas pueden fallar en tiempo de ejecución si su texto JSON en una fila no está formateada correctamente o si no agrega la condición ISJSON en la cláusula WHERE para excluir columnas JSON válidos. 

JSON ya que se representa como texto, no es necesario realizar cambios en el cliente de aplicaciones, esperar a que los nuevos controladores o cambiar protocolo. Puede leer o escribir documentos JSON en las aplicaciones de C#, Java y Node.js como valores de cadena estándar. JSON puede ser cargado en modelos ORM como campos de cadena y enviarse directamente a código del lado del cliente JavaScript mediante peticiones Ajax. Cualquier herramienta ETL también puede cargar o leer JSON porque no hay nuevo formato o interfaz.

Funciones integradas para el procesamiento de JSON

2016 de SQL Server proporciona funciones de análisis y procesamiento de texto JSON. JSON funciones integradas que están disponibles en SQL Server 2016 son:

• ISJSON (jsonText) comprueba si el texto NVARCHAR es bien formateado según la especificación JSON. Puede utilizar esta función para crear restricciones check en columnas NVARCHAR que contienen texto JSON
• JSON_VALUE (jsonText, ruta) analiza jsonText y extrae valores escalares en JavaScript-como ruta de acceso especificada (ver abajo algunos ejemplos de la trayectoria JSON)
• JSON_QUERY (jsonText, camino) que analiza jsonText y extrae objetos o arrays en JavaScript-como ruta de acceso especificada (ver abajo algunos ejemplos de la trayectoria JSON)

Estas funciones utilizan JSON rutas para hacer referencia a valores u objetos de texto JSON. Rutas de JSON utilizan JavaScript-como sintaxis para hacer referencia a propiedades en texto JSON. Algunos ejemplos son:

• '$' –hace referencia a todo objeto JSON en el texto de entrada
• '$.property1' – referencias property1 en objeto JSON
• '$[4]' – referencias 5-to elemento de array JSON (índices se cuentan desde 0 como en JavaScript)
• '$.property1.property2.array1[5].property3.array2[15].property4' – referencias complejo propiedad anidada del objeto JSON
• '$.info. "first name"' – hace referencia a la propiedad "nombre" en objeto de información. Si la clave contiene algunos caracteres especiales tales como espacio, dólar, etc., debe rodearse con comillas dobles.

Signo de dólar ($) representa el entrada objeto JSON (similar a la raíz "/" en lenguaje XPath). Puede agregar cualquier propiedad JavaScript-como o el arreglo de discos después de "$" a las propiedades de la referencia de objeto JSON. Un ejemplo simple de una consulta donde se utilizan estas funciones es:

SELECT Id, FirstName, LastName,

     JSON_VALUE(InfoJSON, '$.info.”social security number”') as SSN,
     JSON_QUERY(InfoJSON, '$.skills') as Skills
FROM Person AS t 
WHERE ISJSON( InfoJSON ) > 0
AND JSON_VALUE(InfoJSON, '$.Type') = 'Student'

Esta consulta devuelve el nombre y el apellido información de estándar de las columnas de una tabla, números de la seguridad social y una matriz de habilidades desde el JSON columna. Los resultados se devuelven desde las filas donde el InfoJSON celda contiene una válida de JSON y el valor del tipo en el JSON columna es "alumno". Como se puede observar, puede usar JSON valores en cualquier parte de la consulta, como ORDER BY, GROUP BY, etc.

JSON en SQL Server 2016: Página 1 de 4

JSON en SQL Server 2016: Página 3 de 4

JSON en SQL Server 2016: Página 4 de 4

Read More

JSON en SQL Server 2016: Página 1 de 4 Páginas

JSON es actualmente uno de los formatos de intercambio de datos el más comúnmente usados. Los servicios más modernos devuelven la información formateada como el texto de JSON; los navegadores web usan la información formateada como JSON. JSON es el formato común cuando la información semiestructurada tiene que ser almacenada, tal como en archivos. Como muchos datos son formateados como JSON, es importante permitir a Servidor SQL tratar el texto JSON recuperado de otros sistemas o formatear la información recuperada de mesas del Servidor SQL como el texto de JSON. 

Como el rasgo más solicitado en Microsoft SQL Server unen el sitio, con más de 1,000 votos, el apoyo al procesamiento del texto JSON ha sido añadido al Servidor SQL 2016. En este correo, presentaremos una descripción de alto nivel de la funcionalidad JSON en el Servidor SQL 2016.

Visión en General

JSON funciones en SQL Server le permiten analizar y consultar datos JSON, transformar JSON formato relacional y exportar los resultados de una consulta SQL como texto JSON.

Si tiene texto JSON, puede extraer datos de JSON o verificar que JSON es formateado correctamente usando funciones integradas JSON_VALUE, JSON_QUERY, and ISJSON. Para consultas y análisis más avanzadas, la función OPENJSON puede transformar una matriz de objetos JSON en un conjunto de filas. Cualquier consulta SQL puede ser ejecutada en el conjunto de resultados devuelto. Finalmente, existe la cláusula de JSON para que le permite dar formato a los resultados de consulta como texto JSON. 

Podemos empezar con ejemplos sencillos. En el siguiente código de Transact-SQL, vamos a definir una variable de texto donde ponemos texto JSON:

DECLARE @json NVARCHAR(4000)
SET @json = 
N'{
    "info":{  
      "type":1,

      "address":{  
        "town":"Bristol",
        "county":"Avon",
        "country":"England"
      },
      "tags":["Sport", "Water polo"]
   },
   "type":"Basic"
}'

Ahora, podemos extraer valores y objetos de texto JSON usando las funciones JSON_VALUE y JSON_QUERY:

SELECT
  JSON_VALUE(@json, '$.type') as type,
  JSON_VALUE(@json, '$.info.address.town') as town,
  JSON_QUERY(@json, '$.info.tags') as tags

Esta consulta devolverá "Basic" y "Bristol" ["Sport", "Water polo"] valores. La función de JSON_VALUE devuelve un valor escalar de texto JSON (cadenas, números, verdadero o falso) que se coloca en un camino JSON especificado como segundo parámetro. JSON_QUERY devuelve un objeto o una matriz (en este ejemplo un conjunto de etiquetas) en el camino JSON. JSON incorporado utilizan JavaScript-como sintaxis para hacer referencia a valores y objetos de texto JSON mediante el segundo parámetro. 

OPEN_JSON la función le permite referencia a alguna matriz en texto JSON y devolver elementos de dicha matriz:

SELECT value
FROM OPENJSON(@json, '$.info.tags')

En este ejemplo, se devuelven los valores de cadena de la matriz de etiquetas. Sin embargo, la función OPEN_JSON puede devolver cualquier objeto complejo. 

Por último, hay una cláusula FOR JSON que puede dar formato a cualquier conjunto de resultados devuelto por la consulta SQL como texto JSON:

SELECT object_id, name
FROM sys.tables
FOR JSON PATH

JSON en SQL Server 2016: Página 2 de 4

JSON en SQL Server 2016: Página 3 de 4

JSON en SQL Server 2016: Página 4 de 4

Read More