Cómo obtener JSON de SQL

¿Sabíais que se pueden obtener datos JSON directamente de las tablas SQL?

Dejadme que os presente dos útiles funciones de SQL, que se utilizan para recuperar datos JSON de consultas SQL - JSON_ARRAY y JSON_OBJECT. 

Podéis usar esas funciones en la sentencia SELECT con otros tipos de elementos de selección, y se pueden especificar en otras ubicaciones en las que se puede utilizar una función SQL, como en una condición WHERE.

La función JSON_ARRAY toma una lista de expresiones separadas por comas y devuelve una matriz JSON conteniendo esos valores.

SELECT TOP 3 Name,
JSON_ARRAY(%ID,%TABLENAME,UCASE(Name),Age,Home_State) "JSON data" 
FROM Sample.Person

SELECT TOP 3 Name,
JSON_ARRAY(%ID,%TABLENAME,UCASE(Name),Age,Home_State ABSENT ON NULL
) "JSON data" 
FROM Sample.Person

Para obtener esas propiedades con valores nulos, hay que usar "NULL ON NULL" en su lugar (y como es lo predeterminado, se puede no añadirlo en ningún caso, como en el primer ejemplo).

Imaginad obtener todos los números de teléfono de los pacientes/clientes en una matriz JSON sin crearla:

SELECT JSON_ARRAY(HomePhone,WorkPhone,Mobile) "JSON data"
FROM Test.Phones

["055-8263371","052-4957286","054-4951066"]

La función JSON_OBJECT toma una lista de expresiones separadas por comas de pares clave:valor y devuelve un objeto JSON conteniendo esos valores.

Se puede especificar cualquier cadena de texto entre comillas simples como un nombre de clave, pero tened en cuenta que no impone convenciones de nomenclatura ni verifica la unicidad de los nombres de clave.

Para el valor, se puede especificar un nombre de columna u otra expresión. Además, tened en cuenta que no permite la sintaxis de asterisco (*) como forma de especificar todos los campos de una tabla.

SELECT TOP 3 JSON_OBJECT('title':'Person from','country':UCASE(Home_State),'table':%TABLENAME,'name':Name,'id':%ID,'age':Age) "JSON data" 
FROM Sample.Person

Para omitir propiedades con valores nulos, solo hay que añadir "ABSENT ON NULL" a la función:

SELECT TOP 3 JSON_OBJECT('title':'Person from','country':UCASE(Home_State),'table':%TABLENAME,'name':Name,'id':%ID,'age':Age ABSENT ON NULL) "JSON data" 
FROM Sample.Person

Para obtener esas propiedades con valores nulos, hay que usar "NULL ON NULL" en su lugar (y como es lo predeterminado, se puede no añadirlo en ningún caso, como en el primer ejemplo).