Análisis y transformación de datos JSON con OPENJSON

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

La función de conjunto de filas OPENJSON convierte texto JSON en un conjunto de filas y columnas. Una vez que transforma una colección de JSON en un conjunto de filas con OPENJSON, puede ejecutar cualquier consulta SQL en los datos devueltos o insertarlos en una tabla de SQL Server.

La función OPENJSON toma un objeto JSON o una colección de objetos JSON y los transforma en una o varias filas. De manera predeterminada, la función OPENJSON devuelve los datos siguientes:

  • Desde un objeto JSON, la función devuelve todos los pares clave-valor que encuentra en el primer nivel.
  • Desde una matriz JSON, la función devuelve todos los elementos de la matriz con sus índices.

Puede agregar una cláusula WITH opcional para proporcionar un esquema que defina explícitamente la estructura de la salida.

Opción 1: OPENJSON con la salida predeterminada

Cuando se usa la función OPENJSON sin proporcionar un esquema explícito para los resultados (es decir, sin una cláusula WITH después de OPENJSON), la función devuelve una tabla con las siguientes tres columnas:

  1. Nombre de la propiedad en el objeto de entrada (o el índice del elemento en la matriz de entrada).
  2. Valor de la propiedad o el elemento de matriz.
  3. Tipo (por ejemplo, cadena, número, booleano, matriz u objeto).

OPENJSON devuelve cada propiedad del objeto JSON o cada elemento de la matriz como una fila independiente.

En el siguiente ejemplo rápido se usa OPENJSON con el esquema predeterminado (es decir, sin la cláusula WITH opcional) y se devuelve una fila por cada propiedad del objeto JSON.

Ejemplo:

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);

Resultados:

key value type
name John 1
surname Doe 1
age 45 2
skills ["SQL","C#","MVC"] 4

Más información sobre OPENJSON con el esquema predeterminado

Para obtener más información y ejemplos, consulte Uso de OPENJSON con el esquema predeterminado (SQL Server).

Para ver la sintaxis y el uso, consulte OPENJSON (SQL Server).

Opción 2: Salida OPENJSON con una estructura explícita

Cuando se especifica el esquema de los resultados con la cláusula WITH de la función JSON, esta devuelve una tabla con las columnas definidas en la cláusula WITH. En la cláusula WITH opcional se puede especificar un conjunto de columnas de salida, sus tipos y las rutas de acceso de las propiedades de origen de JSON de cada valor de salida. OPENJSON iterará por la matriz de objetos JSON, leerá el valor en la ruta de acceso especificada para cada columna y convertirá el valor al tipo especificado.

A continuación, se muestra un ejemplo rápido que usa OPENJSON con un esquema para la salida que especifica explícitamente en la cláusula WITH.

Ejemplo:

DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[  
       {  
         "Order": {  
           "Number":"SO43659",  
           "Date":"2011-05-31T00:00:00"  
         },  
         "AccountNumber":"AW29825",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":1  
         }  
       },  
       {  
         "Order": {  
           "Number":"SO43661",  
           "Date":"2011-06-01T00:00:00"  
         },  
         "AccountNumber":"AW73565",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":3  
         }  
      }  
 ]'  
   
SELECT * FROM  
 OPENJSON ( @json )  
WITH (   
              Number   varchar(200) '$.Order.Number' ,  
              Date     datetime     '$.Order.Date',  
              Customer varchar(200) '$.AccountNumber',  
              Quantity int          '$.Item.Quantity'  
 )

Resultados:

Número Date Customer Cantidad
SO43659 2011-05-31T00:00:00 AW29825 1
SO43661 2011-06-01T00:00:00 AW73565 3

Esta función devuelve los elementos de una matriz JSON y les da formato.

  • Por cada elemento de la matriz JSON, OPENJSON genera una nueva fila en la tabla de salida. Los dos elementos de la matriz JSON se convierten en dos filas en la tabla devuelta.

  • Para cada columna que se especifica mediante la sintaxis colName type json_path, OPENJSON convierte el valor que se encuentra en cada elemento de matriz en la ruta de acceso especificada al tipo especificado. En este ejemplo, se toman los valores de la columna Date de cada elemento en una ruta de acceso $.Order.Date y se convierten en valores de fecha y hora.

Más información sobre OPENJSON con un esquema explícito

Para obtener más información y ejemplos, consulte Uso de OPENJSON con un esquema explícito (SQL Server).

Para ver la sintaxis y el uso, consulte OPENJSON (SQL Server).

OPENJSON requiere el nivel de compatibilidad 130

La función OPENJSON solo está disponible en el nivel de compatibilidad 130. Si el nivel de compatibilidad de la base de datos es inferior a 130, SQL Server no podrá encontrar ni ejecutar la función OPENJSON. Hay otras funciones integradas de JSON que sí están disponibles en todos los niveles de compatibilidad.

Puede comprobar el nivel de compatibilidad en la vista sys.databases o en las propiedades de la base de datos.

Puede cambiar el nivel de compatibilidad de una base de datos mediante el comando siguiente:
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

Más información sobre JSON en SQL Server y Azure SQL Database

Vídeos de Microsoft

Nota:

Es posible que algunos de los vínculos de vídeo de esta sección no funcionen en este momento. Microsoft está migrando contenido que anteriormente estaba en Channel 9 a una nueva plataforma. Actualizaremos los vínculos a medida que los vídeos se migren a la nueva plataforma.

Para obtener una introducción visual a la compatibilidad integrada de JSON en SQL Server y Azure SQL Database, vea los siguientes vídeos:

Consulte también