OPENJSON (Transact-SQL)

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics sql analytics endpointin Microsoft FabricWarehouse in Microsoft FabricSQL Database in Microsoft Fabric SQL Database in Microsoft Fabric

La OPENJSON función con valores de tabla 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 sobre 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. Dado OPENJSON que devuelve un conjunto de filas, puede usar OPENJSON en la FROM cláusula de una instrucción Transact-SQL igual que puede usar cualquier otra función con valores de tabla, vista o tabla.

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

Note

La OPENJSON función 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 puede encontrar ni ejecutar la OPENJSON función. 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

Syntax

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

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

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

openjson

De forma predeterminada, la OPENJSON función con valores de tabla devuelve tres columnas, que contienen el nombre de clave, el valor y el tipo de cada key:value par encontrado en jsonExpression. Como alternativa, puede especificar explícitamente el esquema del conjunto de resultados que OPENJSON devuelve proporcionando with_clause.

with_clause

El with_clause contiene una lista de columnas con sus tipos para OPENJSON devolver. De forma predeterminada, OPENJSON coincide con las claves de jsonExpression con los nombres de columna de with_clause (en este caso, coincide con las claves implica que distingue mayúsculas de minúsculas). Si un nombre de columna no coincide con un nombre de clave, puede proporcionar un column_path opcional, que es una expresión de ruta de acceso JSON que hace referencia a una clave dentro de jsonExpression.

Arguments

jsonExpression

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 jsonExpression:

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);

Results:

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 jsonExpression. OPENJSON busca en el texto JSON en la posición especificada y analiza solo el fragmento al que se hace referencia. Para obtener más información, consulte Expresiones de ruta de acceso JSON.

Puede proporcionar una variable como valor de ruta. (Esto no se admite en SQL Server 2016 (13.x) y versiones anteriores).

En el ejemplo siguiente se devuelve un objeto anidado especificando la ruta de acceso:

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"')

Results

Key	Value
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 del 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

Results

month	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.

with_clause

Define explícitamente el esquema de salida de la OPENJSON función que se va a devolver. El with_clause opcional puede contener los siguientes elementos:

colName

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 name en el esquema, OPENJSON intenta rellenar esta columna con la propiedad "name" en el texto JSON. Puede invalidar esta asignación predeterminada mediante el argumento column_path .

type

Tipo de datos de la columna de salida.

Note

Si también usa la AS JSON opción , el tipo de datos 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, consulte la descripción del parámetro path anteriormente en este tema.

Use column_path para invalidar las reglas de asignación predeterminadas cuando el nombre de una columna de salida no coincide 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, consulte Expresiones de ruta de acceso JSON.

COMO JSON

Use la AS JSON opción en una definición de columna para especificar que la propiedad a la que se hace referencia contiene un objeto o matriz JSON interno. Si especifica la AS JSON opción , 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 la propiedad no se encuentra en la ruta de acceso especificada, la función devuelve NULL en lax modo o devuelve un error en strict modo. Este comportamiento es similar al comportamiento de la JSON_VALUE función.

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

Note

Si desea devolver un fragmento JSON anidado de una propiedad JSON, debe proporcionar la AS JSON marca . Sin esta opción, si no se encuentra la propiedad, OPENJSON devuelve un NULL valor en lugar del objeto o matriz JSON al que se hace referencia, o devuelve un error en tiempo de ejecución en strict modo.

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  
 )

Results

Number	Date	Customer	Quantity	Order
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 OPENJSON función dependen de la WITH opción .

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

  • Key. Valor nvarchar(4000) que contiene el nombre de la propiedad especificada o el índice del elemento de la matriz especificada. La key columna tiene una intercalación BIN2.

  • Value. Valor nvarchar(MAX) que contiene el valor de la propiedad . La value columna hereda su intercalación de jsonExpression.

  • Type. Valor int que contiene el tipo del valor. La Type columna solo se devuelve cuando se usa OPENJSON con el esquema predeterminado. La type columna tiene uno de los siguientes valores:

    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.

• Al llamar a OPENJSON y especificar un esquema explícito en la WITH cláusula , la función devuelve una tabla con el esquema que definió en la WITH cláusula .

Note

Las Keycolumnas , Valuey Type solo se devuelven cuando se usa OPENJSON con el esquema predeterminado y no están disponibles con un esquema explícito.

Remarks

json_path usado en el segundo argumento de OPENJSON o en with_clause puede comenzar con la lax palabra clave o strict .

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

Algunos de los ejemplos de esta página especifican explícitamente el modo de ruta de acceso o laxstrict. El modo de ruta de acceso es opcional. Si no especifica explícitamente un modo de ruta de acceso, lax el modo es el valor predeterminado. Para obtener más información sobre el modo de ruta de acceso y las expresiones de ruta de acceso, consulte Expresiones de ruta de acceso JSON.

Los nombres de columna de with_clause coinciden con las claves del 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. Escape del nombre de la clave en json_path con comillas dobles si tiene caracteres especiales en las teclas. Por ejemplo, $."my key $1".regularKey."key with . dot" coincide con el valor 1 en el texto JSON siguiente:

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

Examples

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 de 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 SalesOrderReasons objetos 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 OPENJSON operador expande la matriz JSON de motivos de ventas como si los motivos se almacenaran en una tabla secundaria independiente. A continuación, el CROSS APPLY operador une cada fila de pedidos de ventas a las filas devueltas por la OPENJSON función con valores de tabla.

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

Tip

Cuando tiene que expandir matrices JSON almacenadas en campos individuales y combinarlas con sus filas primarias, normalmente se usa el operador Transact-SQL CROSS APPLY . Para obtener más información sobre CROSS APPLY, consulta cláusula FROM.

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

Results

title	street	postcode	lon	lat
Mercados de alimentos enteros	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

Contenido relacionado

• Expresiones de ruta de acceso JSON
• Análisis y transformación de datos JSON con OPENJSON
• Uso de OPENJSON con el esquema predeterminado
• Uso de OPENJSON con un esquema explícito