Sifiso Ndlovu

Convertir resultados de SQL Server a JSON

June 2, 2017 by

En mi artículo Almacenamiento de datos formateados en JSON en SQL Server 2016, dimos un vistazo a las opciones T-SQL disponibles para convertir datos JSON a filas y columnas para propósitos de poblar un SQL Server basado en el almacenamiento de datos. La incrementada popularidad de JSON en aplicaciones web modernas puede crear un requerimiento para que los equipos de datos expongan algunos de sus datos a aplicaciones cliente (por ejemplo, herramientas de reportes, servicios web, etc.) en un formato JSON. En este artículo dimos un vistazo a cómo tal requerimiento puede ser implementado por equipos de datos usando la cláusula de SQL Server 2016 FOR JSON.

SQL Server a Tipos de Datos Soportados JSON

Como muchas otras características en SQL Server, hay términos y condiciones para usarlos y JSON no es diferente. Por tanto, es importante que tomemos nota de los tipos de datos soportados. Los datos SQL Server almacenados en los siguientes tiempos de datos no pueden ser convertidos a JSON:

Un desglose de los tipos de datos soportados es mostrado en la Tabla 1.  

SQL Server Data Type JSON Data Type
char, nchar, varchar, nvarchar, date, datetime, datetime2, time, datetimeoffset, uniqueidentifier, money string
int, bigint, float, decimal, numeric number
Bit Boolean
varbinary, binary, image, timestamp, rowversion BASE64-encoded string
Tabla 1

Cláusula T-SQL FOR JSON

Aunque el soporte de SQL Server para XML permitió la representación gráfica de datos vía un editor (mostrado en la Figura 1), intentar ver datos JSON vía un editor puede ser frustrante, ya que los datos JSON son mostrados en una sola fila sin formato.


Figura 1


Figura 2

Es por tanto recomendable que mientras usted aprende JSON en SQL Server que encuentre un editor de JSON. Para los propósitos de esta discusión, estaré usando JSONFormatter from curiousconcept.com. Como puede verse en la Figura 3, la salida JSON de la Figura 2 es ahora apropiadamente formateada.


Figura3

Hay dos maneras de que los resultados relacionales puedan ser convertidos a JSON: las opciones AUTO y PATH.

  1. Convertir los Resultados Usando el Modo AUTO

    Esta es la manera más simple de convertir datos relacionales a formato JSON, ya que todo lo que tiene que hacer es añadir la cláusula FOR JSON AUTO al final de su sentencia SELECT. En este modo, la estructura de la salida JSON es deterinada por una combinación del orden de las columnas en su sentencia SELECT, así como las tablas que son referenciadas por la sentencia SELECT. La Figura 4 muestra una sentencia T-SQL que convierte los resultados de nuestros datos ficticios de Fruit Sales a JSON.

    Figura 4

    Los resultados del script de arriba son mostrados a continuación. En estos se muestra un solo arreglo (representado por un corchete) fue retornado con filas mantenidas como objetos (representados por llaves).


    Figura 5

    El script de muestra provisto en la Figura 4 no demuestra completamente el rol del ordenamiento en la columna y la tabla en la cláusula FOR JSON AUTO, ya que una sola tabla fue usada. La Figura 6 muestra el script revisado con una unión a otra dimensión de búsqueda de cliente ficticio respecto de los clientes que han comprado frutas.

    Figura 6

    La ejecución del script anterior resulta en una salida que es mostrada en la Figura 7. Usted notará que otro arreglo hijo (con sus propios objetos) etiquetado cust aparece en la salida. El arreglo hijo representa la información recuperada de la dimensión de cliente.


    Figura 7

    De todas maneras, cuando cambiamos el orden de la columna de la sentencia SELECT de tal manera que comience con una columna de la dimensión de cliente como se muestra en la Figura 8, obtenemos una salida diferente que la que tenemos en la Figura 7, en la cual el arreglo hijo está ahora basado en la dimensión FruitSales.

    Figura 8


    Figura 9

    Por tanto, aunque usar el modo AUTO es conveniente, a menudo retorna una salida inconsistente, cuyo orden es sujeto a cambios basados en el orden de la columna en la sentencia SELECT. Para asegurarse que los resultados de su JSON son consistentes, usted tendrá que hacer uso del modo PATH.

  2. Convertir Resultados Usando el Modo Path

    El modo PATH puede ser usado en dos maneras:

    1. Sin una sintaxis de punto

    2. Con una sintaxis de punto

    Cuando usted está usando sin una sintaxis de punto, trabaja de manera similar al modo AUTO en que generará una salida JSON basada en el orden de las columnas en su sentencia SELECT.

    Figura 10

    De todos modos, como puede ser visto en la Figura 11, el modo PATH no agrupa automáticamente la información de tablas unidas a arreglos hijo. De hecho, todas las columnas de las dos tablas son mostradas en el mismo nivel raíz.


    Figura 11

    Para organizar la salida JSON en arreglos hijo, usted tendrá que usar la sintaxis de punto como se muestra en la Figura 12. La etiqueta antes del punto representa el nombre del objeto – en este caso, tenemos dos objetos llamados Sales y Cust.

    Figura 12

    The execution results of Figure 12 are shown below.


    Figura 13

    Usted aún puede organizar su salida JSON PATH a arreglos hijo convirtiendo su script a una unión anidada, como se muestra en la Figura 14.

    Figura 14


    Figura 15

    Opciones de la cláusula FOR JSON

    Los modos AUTO y BOTH le permiten especificar opciones adicionales como ROOT, INCLUDE_NULL_VALUES and WITHOUT_ARRAY_WRAPPER.

    1. ROOT

      La opción ROOT es usada para asignar una etiqueta al arreglo de nivel superior. La Figura 16 muestra la aplicación de la opción ROOT.

      Figura 16

      Como puede verse en la Figura 17, nuestro arreglo principal es ahora titulado TOP_LEVEL.


      Figure 17

    2. INCLUDE_NULL_VALUES 

      El comportamiento por defecto cuando se usa la cláusula FOR JSON es que los valores NULL no serán incluidos en su salida JSON. Esto puede ser sobrescrito al especificar la opción INCLUDE_NULL_VALUES. Para ilustrar este punto, he añadido una transacción falsa de frutas en mi sentencia SELECT, como se muestra en la Figura 18. Usted notará que los últimos cuatro valores de mi transacción falsa son NULL.

      Figura 18

      La salida de esta sentencia es mostrada en la Figura 19, en la cual los últimos 4 valores que eran NULL no son incluidos en la salida JSON.


      Figura 19

      Para sobrescribir este comportamiento, usted sólo necesita incluir la opción INCLUDE_NULL_VALUES como se muestra en la Figura 20.

      Figura 20

      Como puede verse ahora en la Figura 21, después de incluir la opción INCLUDE_NULL_VALUES, nuestra salida JSON incluye valores NULL.


      Figura 21

    3. WITHOUT_ARRAY_WRAPPER

      Por defecto, cada cláusula FOR JSON retorna datos JSON entre corchetes – también conocidos como arreglo. Hay instancias donde usted desea que los corchetes sean excluidos de la salida porque puede que desee concatenar dos o más datos JSON. La Figura 22 muestra la aplicación de la opción WITHOUT_ARRAY_WRAPPER.

      Figura 22

      Los resultados del script anterior son mostrados abajo.


      Figura 23

    Finalmente, usted habría notado en la Figura 22 que usé una cláusula TOP para limitar mi selección a una sola fila. Esto es porque cuando usted usa la opción WITHOUT_ARRAY_WRAPPER contra un dataset que tiene más de una fila, usted se encontrará con un error de validación JSON (mostrado abajo) causado por los corchetes faltantes. Por tanto, sea cuidadoso en la manera en que usa la opción WITHOUT_ARRAY_WRAPPER, ya que puede llevar a errores de validación no intencionales.


    Figura 24

    Descargas

    Base de datos de muestra selectSIFISOBlogs

    Reference


    Sifiso Ndlovu
    Últimas entradas de Sifiso Ndlovu (ver todo)

    Related posts:

    1. Convert SQL Server results into JSON
    2. Almacenamiento de datos formateados en JSON en SQL Server 2016
    3. Cómo importar / exportar datos JSON usando SQL Server 2016
    4. Warehousing JSON formatted data in SQL Server 2016
    5. How to import/export JSON data using SQL Server 2016
168 Views