JSON es una sigla que significa JavaScript Object Notation. JSON es muy utilizado y popular por lo que actualmente es el formato de intercambio de datos más utilizado. La mayoría de los servicios web y móviles más modernos proporcionan información en formato de texto JSON. Todos los servicios web de bases de datos, navegadores web (Firefox, Internet Explorer) devuelven resultados con formato de texto JSON o aceptan datos con formato JSON. Debido a que la información del formato de sistemas externos como texto JSON, este también se almacena en SQL Server 2016 como texto. Así mismo puede usar columnas NVARCHAR estándar para almacenar datos JSON en SQL Server 2016.

Este artículo nos aclara cómo importar datos JSON a la tabla de SQL Server 2016 y cómo exportar datos de la tabla de SQL Server 2016 como JSON utilizando las funciones integradas de SQL Server 2016.

Con SQL Server 2016, las funciones integradas pueden analizar el texto JSON para leer o modificar valores JSON, transformar la matriz de objetos JSON en formato de tabla, cualquier consulta Transact-SQL puede generarse sobre los objetos JSON convertidos, los resultados de las consultas Transact-SQL pueden ser formateado en formato JSON.

Entonces, comencemos. A continuación, se muestra un ejemplo simple de JSON:

{
  ”BusinessEntityID”:1,
  ”NationalIDNumber”:”295847284″,
  ”JobTitle”:”Chief Executive Officer”,
  ”BirthDate”:”1969-01-29″,
  ”Gender”:”M”
}

Se puede encontrar más instrucciones sobre la estructura del JSON en este enlace.

Vamos a declarar una variable de SQL Server y poner código JSON en ella.

Es importante indicar que una de las funciones integradas de JASON que se implementan en SQL Server 2016 es ISJSON.

Para tal fin la función ISJSON verifica si es el código en la variable @json formateada como JSON. Si el código en la variable @json, formatea correctamente el valor de salida en la cuadrícula de resultados, entonces aparecerá 1:

De lo contrario, el valor de salida en la cuadrícula de resultados será 0. Por ejemplo, si se omite el corchete abierto en el ejemplo anterior, el resultado obtenido será:

Es importante indicar que para convertir el texto JSON en un conjunto de filas y columnas, se utiliza la función OPENJSON.

Es muy necesaria la sintaxis de la función OPENJSON que transforma el texto JSON en filas y columnas se ve así:

OPENJSON (<texto json>)
WITH (<columna / tipo>)

En la cláusula WITH, se precisa el esquema de las filas devueltas con el nombre de las columnas y sus tipos. La función OPENJSON analizará el objeto JSON, permitirá coincidir las propiedades en el objeto JSON con los nombres de columna en la cláusula WITH y cambiará sus valores en tipos específicos.

En el siguiente ejemplo, se muestra cómo convertir texto JSON a un conjunto de filas y columnas:

El resultado se verá como sigue:

Si se ejecuta la instrucción SELECT sin una cláusula WITH:

Aparecerá el siguiente resultado:

1. columna clave contiene el nombre de la propiedad
2. La columna de valor contiene el valor de la propiedad
3. tipo columna contiene el tipo de datos del valor

La columna de tipo tiene seis valores para los tipos de datos:

Para obtener un valor escalar del texto JSON y usarlo en las consultas Transact-SQL, use la función JSON_VALUE . Accedamos a un valor de la propiedad BusinessEntityID desde la variable @jason y lo utilizamos en la cláusula WHERE para recuperar algunos valores de la tabla Person.Person en la base de datos AdventureWorks 2014. Pega y ejecuta el siguiente código:

Los resultados se verán como sigue:

Un signo de dólar $ se usa para hacer referencia (acceso) a las propiedades, objetos en texto JSON. Si se omite en la consulta:

El siguiente error puede ocurrir:

Msg 13607, Nivel 16, Estado 3, Línea 14
Si la ruta JSON no está formateada correctamente. El carácter inesperado “B” se encuentra en la posición 0.

Es importante indicar que para extraer una matriz o un objeto de un texto JSON, use la función JSON_QUERY. Vamos a ejecutar la consulta que contiene la función JSON_QUERY:

El resultado logrado será:

El valor NULL aparece y se devuelve porque la función JSON_QUERY funciona con matrices y objetos que no tienen valores escalares. Para ver el mensaje de error en lugar del valor NULO, escriba la palabra strict antes del signo de dólar:

Considere que cuando se ejecute el código anterior, aparecerá el siguiente mensaje de error:

Mensaje 13624, Nivel 16, Estado 1, Línea 12
Por ello no se puede encontrar el objeto o la matriz en la ruta JSON especificada.

Para tal fin incluiremos el objeto de contacto en la variable @json y utilizaremos la función JSON_QUERY:

Entonces aparecerán los siguientes resultados:

Para el almacenamiento de datos JSON en SQL Server 2016

Se debe insertar datos en alguna tabla de SQL Server usando datos de @json de la misma forma que en T-SQL normal. Ejecute el siguiente código:

Luego aparecerán los siguientes resultados:

Como puede se puede ver, la columna de contacto en la tabla de la persona tiene un valor NULO en lugar de {“Inicio”: ”036 / 222-333 ″,” Mob ”:” 064/3376222 ″}.

Con objeto de insertar valores del objeto de contacto en la variable @json en la columna de contacto debajo de la tabla de persona, entonces se debe usar la cláusula AS JSON. Luego pongamos esta cláusula en un código y ejecutemos:

Pero esta vez aparecerá un mensaje de error:

Mensaje 13618, Nivel 16, Estado 1, Línea 30
Es importante indicar en la opción AS JSON que se puede especificar solo para la columna de tipo nvarchar (max) en la cláusula WITH.

Como dice el mensaje, la opción AS JASON solo admite el tipo de datos nvarchar(max). Entonces cambiemos el tipo de datos para la columna de contacto y ejecutemos la consulta nuevamente. Después de cambiar el tipo de datos de la columna de contacto de varchar (max) a nvarchar(max) y al ejecutarlo, aparecerán los siguientes resultados:

Exportando datos de SQL Server 2016 como JSON

Para dar formato o al exportar los resultados de la consulta como JSON, se debe usar la cláusula FOR JSON con el modo PATH o AUTO. Al exportar estos resultados de la consulta a JSON, se debe usar uno de los modos con la cláusula FOR JSON, de lo contrario se producirá el siguiente error:

Mensaje 102, Nivel 15, Estado 1, Línea 7
Sintaxis incorrecta cerca de ‘JSON’.

Vale la oportunidad de indicar que la principal diferencia entre el modo PATH y AUTO es que, con el modo PATH, un usuario tiene un control total sobre cómo formatear la salida JSON, mientras que con el modo AUTO, la cláusula FOR JSON formateará automáticamente la salida JSON basándose en la estructura de la sentencia SELECT.

Modo PATH

Consiguientemente vamos a usar un ejemplo simple para demostrar lo que puede hacer el modo PATH con la cláusula FOR JSON. En este ejemplo, la tabla Pesron.Person se usa desde la base de datos AdventureWorks 2014. Vale la pena indicar que, en un editor de consultas, el siguiente código debe ser pegado y ejecutado:

La salida JSON será:

[
  {
     “BusinessEntityID”:1,
     “FirstName”:”Ken”,
     “LastName”:”Sánchez”,
     “MiddleName”:”J”
  },
   {
     “BusinessEntityID”:2,
     “FirstName”:”Terri”,
     “LastName”:”Duffy”,
     “MiddleName”:”Lee”
   },
   {
     “BusinessEntityID”:3,
     “FirstName”:”Roberto”,
     “LastName”:”Tamburello”
  }
]

Es importante indicar que, si se da cuenta, el “Título” y en algunas secciones las propiedades de “MiddleName” no aparecen en la salida JSON. Esto se debe a que “MiddleName” y “Title” contienen valores nulos. De forma predeterminada, los valores nulos no se incluyen en la salida JSON. Para incluir valores nulos de los resultados de la consulta en la salida JSON, se debe usar la opción INCLUDE_NULL_VALUES.

Incluyamos la opción INCLUDE_NULL_VALUES en un ejemplo y ejecutemos la consulta:

El resultado será:

[
   {
     “BusinessEntityID”:1,
     “FirstName”:”Ken”,
     “LastName”:”Sánchez”,
     “Title”:null,
     “MiddleName”:”J”
   },
   {
     “BusinessEntityID”:2,
     “FirstName”:”Terri”,
     “LastName”:”Duffy”,
     “Title”:null,
     “MiddleName”:”Lee”
   },
   {
     “BusinessEntityID”:3,
     “FirstName”:”Roberto”,
     “LastName”:”Tamburello”,
     “Title”:null,
     “MiddleName”:null
  }
]

Al utilizar el modo PATH, se puede usar la sintaxis de puntos, por ejemplo, ‘Item.Title’ para dar formato a la salida JSON anidada. Por ejemplo, agreguemos alias para las columnas Title y MiddleName:

La salida JSON será:

[
   {
     “BusinessEntityID”:1,
     “FirstName”:”Ken”,
     “LastName”:”Sánchez”,
     “Item”:{
       “Title”:null,
       “MiddleName “:”J”
     }
   },
   {
     “BusinessEntityID”:2,
     “FirstName”:”Terri”,
     “LastName”:”Duffy”,
     “Item”:{
       “Title”:null,
       “MiddleName “:”Lee”
     }
   },
   {
     “BusinessEntityID”:3,
     “FirstName”:”Roberto”,
     “LastName”:”Tamburello”,
     “Item”:{
       “Title”:null,
       “MiddleName”:null
     }
   }
]

Por ello como se puede ver, la salida JSON ahora contiene el objeto “Elemento” y las propiedades “Título” y “Nombre central” dentro de él.

Modo automático

En el modo AUTO se generará automáticamente la salida JSON en función del orden de las columnas en la instrucción SELECT.

Por ejemplo, si utilizamos el ejemplo anterior y en lugar de PATH, ponemos el modo AUTO después de la cláusula FOR JSON

El resultado será:

[
   {
     “BusinessEntityID”:1,
     “FirstName”:”Ken”,
     “LastName”:”Sánchez”,
     “Item.Title”:null,
     “Item.MiddleName”:”J”
   },
   {
     “BusinessEntityID”:2,
     “FirstName”:”Terri”,
     “LastName”:”Duffy”,
     “Item.Title”:null,
     “Item.MiddleName”:”Lee”
   },
   {
     “BusinessEntityID”:3,
     “FirstName”:”Roberto”,
     “LastName”:”Tamburello”,
     “Item.Title”:null,
     “Item.MiddleName”:null
   }
]

Es importante indicar que el modo AUTO en este caso cuando se usa tendrá los resultados de una tabla que no creará la salida JSON anidada y el separador de puntos se tratará como la clave con puntos. Sin embargo, pero cuando se unen dos tablas, las columnas de la primera tabla se tratarán como las propiedades del objeto raíz y las columnas de la segunda tabla se tratarán como las propiedades de un objeto anidado. Por ello se utilizará un nombre de tabla o alias de la segunda tabla como nombre de la matriz anidada:

Cuando se ejecuta la siguiente consulta:

El resultado será:

[
   {
     “TerritoryID”:2,
     “Territory”:”Northeast”,
     “s”:[
        {
          “Name”:”Wholesale Bikes”
        },
        {
          “Name”:”Wheelsets Storehouse”
        },
        {
          “Name”:”Weekend Tours”
        },
        {
          “Name”:”Wholesale Bikes”
        },
        {
          “Name”:”Weekend Tours”
        },
        {
          “Name”:”Wheelsets Storehouse”
        }
     ]
  }
]

Ver también:

• JSON en SQL Server 2016: Parte 1 de 4
• Convertir los resultados de SQL Server en JSON
• Datos JSON (SQL Server)
• Expresiones de ruta JSON (SQL Server)

• Acerca de
• Últimas entradas

Marko Zivkovic

Marko es un ingeniero mecánico a quien le gusta jugar basketball, football (table-soccer) y escuchar música rock. Está interesado en código SQL, desarrollo PHP y técnicas en HTML y CSS.

Actualmente trabajando para ApexSQL LLC como un Ingeniero de Ventas de Software, él está ayudando a los clientes con problemas técnicos y hace aseguramiento de calidad para los complementos ApexSQL Complete, ApexSQL Refactor y ApexSQL Search.

Ver todas las entradas de Marko Zivkovic

Últimas entradas de Marko Zivkovic (ver todo)

• Cómo poder consultar los datos de Excel utilizando servidores vinculados a SQL Server - August 1, 2019
• Cómo poder importar un archivo plano a una base de datos de SQL Server usando el asistente para Importar un archivo plano - August 1, 2019
• Tablas temporales – SQL Server - August 1, 2019