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