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. Para obtener más información sobre cómo trabajar con datos JSON en la Motor de base de datos de SQL Server, vea Datos JSON en 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.

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. name de la propiedad en el objeto de entrada (o el índice del elemento en la matriz de entrada).
  2. value de la propiedad o el elemento de matriz.
  3. type (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.

DECLARE @json NVARCHAR(MAX);

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

SELECT *
FROM OPENJSON(@json);

El conjunto de resultados es el siguiente:

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

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

Para ver la sintaxis y el uso, consulte OPENJSON.

Salida OPENJSON con una estructura explícita

Cuando se especifica el esquema de los resultados con la cláusula WITH de la función OPENJSON, 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.

En el ejemplo siguiente se usa OPENJSON con un esquema para la salida que se especifica explícitamente en la cláusula WITH.

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-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'
);

El conjunto de resultados es el siguiente:

Número Date Customer Cantidad
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-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.

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.

OPENJSON requiere el nivel de compatibilidad 130

La función OPENJSON solo está disponible en el nivel de compatibilidad 130 y superior. 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 de sys.databases o en las propiedades de la base de datos y cambiar el nivel de compatibilidad de una base de datos mediante el comando siguiente:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Contenido relacionado