Compartir por

OPENJSON (Transact-SQL)

  • Artigo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores de Azure SQL Database Azure SQL Instancia administrada punto de conexión de SQL Analytics de Azure Synapse Analytics en Microsoft Fabric Warehouse en Microsoft Fabric

OPENJSON es una función con valores de tabla que analiza el texto JSON y devuelve objetos y propiedades de la entrada JSON como filas y columnas. En otras palabras, OPENJSON proporciona una vista de conjunto de filas de un documento JSON. Las columnas del conjunto de filas y las rutas de acceso de propiedades JSON que se usan para rellenar las columnas se pueden especificar de forma explícita. Como OPENJSON devuelve un conjunto de filas, se puede usar OPENJSON en la cláusula FROM de una instrucción Transact-SQL al igual que se puede usar cualquier otra tabla, vista o función con valores de tabla.

Use OPENJSON para importar datos JSON a SQL Server o convertir datos JSON a formato relacional para una aplicación o servicio que no pueda usar JSON directamente.

Nota

La función OPENJSON solo está disponible en el nivel de compatibilidad 130 o 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 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. Se puede cambiar el nivel de compatibilidad de una base de datos mediante el comando siguiente:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Convenciones de sintaxis de Transact-SQL

Sintaxis

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

La función con valores de tabla OPENJSON analiza la expresiónJSON proporcionada como primer argumento y devuelve una o varias filas que contienen datos de los objetos JSON en la expresión. expresiónJSON puede contener objetos secundarios anidados. Si quiere analizar un subobjeto desde expresiónJSON, puede especificar un parámetro path para el subobjeto JSON.

openjson

Sintaxis de TVF de OPENJSON

De forma predeterminada, la función con valores de tabla OPENJSON devuelve tres columnas, que contienen el nombre de clave, el valor y el tipo de cada par de {clave: valor} que se encuentra en expresiónJSON. Como alternativa, se puede especificar explícitamente el esquema del conjunto de resultados que OPENJSON devuelve proporcionando cláusula_with.

cláusula_with

Sintaxis de la cláusula WITH en TVF OPENJSON

La cláusula_with contiene una lista de columnas con sus tipos que OPENJSON tiene que devolver. De forma predeterminada, OPENJSON hace coincidir las claves en expresiónJSON con los nombres de columna en la cláusula_with (en este caso, la coincidencia de claves implica que distingue mayúsculas de minúsculas). Si un nombre de columna no coincide con un nombre de clave, se puede proporcionar una función opcional column_path, que es una expresión de ruta de acceso JSON que hace referencia a una clave en la expresiónJSON.

Argumentos

expresiónJSON

Es una expresión de caracteres Unicode que contiene texto JSON.

OPENJSON recorre en iteración los elementos de la matriz o las propiedades del objeto en la expresión JSON y devuelve una fila por cada elemento o propiedad. En el ejemplo siguiente se devuelve cada propiedad del objeto proporcionado como expresiónJSON:

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Resultados:

key value type
String_value John 1
DoublePrecisionFloatingPoint_value 45 2
DoublePrecisionFloatingPoint_value 2.3456 2
BooleanTrue_value true 3
BooleanFalse_value false 3
Null_value NULL 0
Array_value ["a","r","r","a","y"] 4
Object_value {"obj":"ect"} 5
  • DoublePrecisionFloatingPoint_value se ajusta a IEEE-754.

path

Es una expresión de ruta de acceso JSON opcional que hace referencia a un objeto o una matriz dentro de expresiónJSON. OPENJSON busca en el texto de JSON en la posición especificada y analiza solo el fragmento al que se hace referencia. Para más información, vea Expresiones de ruta de acceso JSON (SQL Server).

En SQL Server 2017 (14.x) y en Azure SQL Database, puede proporcionar una variable como el valor de path.

En el ejemplo siguiente se devuelve un objeto anidado especificando la path:

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

Resultados

Clave Valor
0 en-GB
1 en-UK
2 de-AT
3 es-AR
4 sr-Cyrl

Cuando OPENJSON analiza una matriz JSON, la función devuelve los índices de los elementos en el texto JSON como claves.

La comparación que se usa para hacer coincidir los pasos de ruta de acceso con las propiedades de la expresión JSON distingue mayúsculas de minúsculas, e ignora la intercalación (es decir, una comparación BIN2).

Identidad de elementos de matriz

La función OPENJSON del grupo de SQL sin servidor de Azure Synapse Analytics puede generar automáticamente la identidad de cada fila que se devuelve como resultado. La columna de identidad se especifica mediante la expresión $.sql:identity() en la ruta de acceso JSON después de la definición de columna. La columna con este valor en la expresión de ruta de acceso JSON generará un número único basado en 0 para cada elemento de la matriz JSON que analiza la función. El valor de identidad representa la posición o el índice del elemento de matriz.

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

Resultados

mes temp month_id
Jan 10 0
Feb 12 1
Mar 15 2
Apr 17 3
May 23 4
Jun 27 5

La identidad solo está disponible en el grupo de SQL sin servidor de Synapse Analytics.

cláusula_with

Define explícitamente el esquema de salida que va a devolver la función OPENJSON. La cláusula_with opcional puede contener los elementos siguientes:

colName es el nombre de la columna de salida.

De forma predeterminada, OPENJSON usa el nombre de la columna para que coincida con una propiedad en el texto JSON. Por ejemplo, si especifica la columna nombre en el esquema, OPENJSON intenta rellenar esta columna con la propiedad "nombre" en el texto JSON. Puede invalidar esta asignación predeterminada mediante el argumento column_path.

type
Es el tipo de datos para la columna de salida.

Nota

Si también usa la opción AS JSON, el tipo de columna debe ser NVARCHAR(MAX).

column_path
Es la ruta de acceso JSON que especifica la propiedad que se va a devolver en la columna especificada. Para obtener más información, vea la descripción del parámetro path anteriormente en este tema.

Use ruta_de_columna para invalidar las reglas de asignación predeterminadas cuando el nombre de una columna de salida no coincida con el nombre de la propiedad.

La comparación que se usa para hacer coincidir los pasos de ruta de acceso con las propiedades de la expresión JSON distingue mayúsculas de minúsculas, e ignora la intercalación (es decir, una comparación BIN2).

Para obtener más información sobre las rutas de acceso, vea Expresiones de ruta de acceso JSON (SQL Server).

AS JSON
Use la opción AS JSON en una definición de columna para especificar que la propiedad a la que se hace referencia contiene un objeto JSON interno o una matriz. Si se especifica la opción AS JSON, el tipo de la columna debe ser NVARCHAR(MAX).

  • Si no se especifica AS JSON para una columna, la función devuelve un valor escalar (por ejemplo, int, string, true, false) de la propiedad JSON especificada en la ruta de acceso especificada. Si la ruta de acceso representa un objeto o una matriz, y no se puede encontrar la propiedad en la ruta de acceso especificada, la función devuelve NULL en modo lax o devuelve un error en modo strict. Este comportamiento es similar al de la función JSON_VALUE.

  • Si se especifica AS JSON para una columna, la función devuelve un fragmento de JSON de la propiedad JSON especificada en la ruta de acceso especificada. Si la ruta de acceso representa un valor escalar y no se puede encontrar la propiedad en la ruta de acceso especificada, la función devuelve NULL en modo lax o devuelve un error en modo strict. Este comportamiento es similar al de la función JSON_QUERY.

Nota

Si quiere devolver un fragmento de JSON anidado de una propiedad JSON, tendrá que proporcionar la marca AS JSON. Sin esta opción, si no se puede encontrar la propiedad, OPENJSON devuelve un valor NULL en lugar de la matriz o el objeto JSON al que se hace referencia, o bien devuelve un error en tiempo de ejecución en modo strict.

Por ejemplo, en la consulta siguiente se devuelve y se da formato a los elementos de una matriz:

DECLARE @json NVARCHAR(MAX) = 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',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

Resultados

Number Date Customer Cantidad Pedido de
SO43659 2011-05-31T00:00:00 AW29825 1 {"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661 2011-06-01T00:00:00 AW73565 3 {"Number":"SO43661","Date":"2011-06-01T00:00:00"}

Valor devuelto

Las columnas que devuelve la función OPENJSON dependen de la opción WITH.

  1. Cuando se llama a OPENJSON con el esquema predeterminado, es decir, cuando no se especifica un esquema explícito en la cláusula WITH, la función devuelve una tabla con las columnas siguientes:

    1. Clave. Un valor nvarchar(4000) que contiene el nombre de la propiedad especificada o el índice del elemento en la matriz especificada. La columna de clave tiene una intercalación BIN2.

    2. Valor. Un valor nvarchar(max) que contiene el valor de la propiedad. La columna Valor hereda la intercalación de expresiónJSON.

    3. Tipo. Un valor int que contiene el tipo del valor. La columna Tipo solo se devuelve cuando se usa OPENJSON con el esquema predeterminado. La columna Tipo tiene uno de los valores siguientes:

      Valor de la columna Tipo Tipo de datos JSON
      0 null
      1 string
      2 number
      3 true/false
      4 array
      5 object

    Solo se devuelven las propiedades de primer nivel. Se produce un error en la instrucción si el texto JSON no tiene el formato correcto.

  2. Cuando se llama a OPENJSON y se especifica un esquema explícito en la cláusula WITH, la función devuelve una tabla con el esquema que se haya definido en la cláusula WITH.

Nota

Las columnas Clave, Valor y Tipo se devuelven solo cuando se usa OPENJSON con el esquema predeterminado y no están disponibles con un esquema explícito.

Observaciones

La json_path que se usa en el segundo argumento de OPENJSON o en la cláusula_with puede comenzar con la palabra clave lax o strict.

  • En modo lax, OPENJSON no genera un error si no se encuentra el objeto o el valor en la ruta de acceso especificada. Si no se puede encontrar la ruta de acceso, OPENJSON devuelve un conjunto de resultados vacío o un valor NULL.
  • En modo strict, OPENJSON devuelve un error si no se encuentra la ruta de acceso.

En algunos de los ejemplos de esta página se especifica explícitamente el modo de ruta de acceso, lax o strict. El modo de ruta de acceso es opcional. Si no especifica explícitamente un modo de ruta de acceso, el modo lax es el valor predeterminado. Para más información sobre las expresiones de modo de ruta de acceso y de ruta de acceso, vea Expresiones de ruta de acceso JSON (SQL Server).

Los nombres de columna en la cláusula_with se hacen coincidir con las claves en el texto JSON. Si se especifica el nombre de columna [Address.Country], se compara con la clave Address.Country. Si quiere hacer referencia a una clave anidada Country dentro del objeto Address, tendrá que especificar la ruta de acceso $.Address.Country en la ruta de acceso de columna.

json_path puede contener claves con caracteres alfanuméricos. Use comillas dobles como caracteres de escape para el nombre de clave en json_path si hay caracteres especiales en las claves. Por ejemplo, $."my key $1".regularKey."key with . dot" coincide con el valor 1 en el siguiente texto JSON:

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

Ejemplos

Ejemplo 1: conversión de una matriz JSON en una tabla temporal

En el ejemplo siguiente se proporciona una lista de identificadores como una matriz JSON de números. La consulta convierte la matriz JSON en una tabla de identificadores y filtra todos los productos con los identificadores especificados.

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

Esta consulta equivale al ejemplo siguiente. Pero en el ejemplo siguiente, se deben insertar números en la consulta en lugar de pasarlos como parámetros.

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

Ejemplo 2: combinación de propiedades de dos objetos JSON

En el ejemplo siguiente se selecciona una unión de todas las propiedades de dos objetos JSON. Los dos objetos tienen una propiedad nombre duplicada. En el ejemplo se usa el valor de clave para excluir la fila duplicada de los resultados.

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

Ejemplo 3: combinación de filas con datos JSON almacenados en celdas de tabla con CROSS APPLY

En el ejemplo siguiente, la tabla SalesOrderHeader tiene una columna de texto SalesReason que contiene una matriz de SalesOrderReasons en formato JSON. Los objetos SalesOrderReasons contienen propiedades como Calidad y Fabricante. En el ejemplo se crea un informe que combina todas las filas de pedido de ventas y las razones de ventas relacionadas. El operador OPENJSON expande la matriz JSON de razones de venta como si las razones se almacenaran en una tabla secundaria independiente. Después, el operador CROSS APPLY combina cada fila de pedido de ventas con las filas devueltas por la función con valores de tabla OPENJSON.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

Sugerencia

Cuando es necesario expandir matrices JSON almacenadas en campos individuales y combinarlas con sus filas primarias, normalmente se usa el operador CROSS APPLY de Transact-SQL. Para obtener más información sobre CROSS APPLY, vea FROM (Transact-SQL).

Se puede rescribir la misma consulta mediante OPENJSON con un esquema definido explícitamente de las filas que se van a devolver:

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

En este ejemplo, la ruta de acceso $ hace referencia a cada elemento de la matriz. Si quiere convertir explícitamente el valor devuelto, puede usar este tipo de consulta.

Ejemplo 4: combinación de filas relacionales y elementos de JSON con CROSS APPLY

En la consulta siguiente se combinan filas relacionales y elementos de JSON en los resultados mostrados en la tabla siguiente.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

Resultados

title street postcode lon lat
Whole Food Markets 17991 Redmond Way WA 98052 47.666124 -122.10155
Sears 148th Ave NE WA 98052 47.63024 -122.141246,17

Paso 5: importación de datos JSON a tablas de SQL Server

En el ejemplo siguiente se carga un objeto JSON completo en una tabla de SQL Server .

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  
   
  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

Ejemplo 6: Ejemplo sencillo con contenido JSON

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

Consulte también