Did you know that you can get JSON data directly from your SQL tables?

Let me introduce you to 2 useful SQL functions that are used to retrieve JSON data from SQL queries - JSON_ARRAY and JSON_OBJECT. 
You can use those functions in the SELECT statement with other types of select items, and they can be specified in other locations where an SQL function can be used, such as in a WHERE clause

The JSON_ARRAY function takes a comma-separated list of expressions and returns a JSON array containing those values.

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

To  To omit null values properties, just add "ABSENT ON NULL" to the function:

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

To get those null-valued properties, use "NULL ON NULL" instead (and since this is the default, you can just not add it at all like the first example).

Imaging getting all your patient/customer phones in one JSON array without building it:

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

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

The JSON_OBJECT function takes a comma-separated list of key:value pairs and returns a JSON object containing those values.
You can specify any single-quoted string as a key name, but note that it doesn't enforce any naming conventions or uniqueness check for key names.
For the value, you can specify a column name or other expression. In addition, note that it doesn't support the asterisk (*) syntax as a way to specify all fields in a table.

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

To omit null values properties, just add "ABSENT ON NULL" to the function:

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

To get those null-valued properties, use "NULL ON NULL" instead (and since this is the default, you can just not add it at all).