New post
Article
· Jan 22, 2024 2m read

Getting JSON from SQL

#Best Practices #JSON #SQL #InterSystems IRIS #InterSystems IRIS for Health

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
SQL
SQL
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
SQL
SQL

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
SQL
SQL
["055-8263371","052-4957286","054-4951066"]
Shell Session
Shell Session

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
SQL
SQL

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
SQL
SQL

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

Discussion (11)3
Log in or sign up to continue
Sylvain Guilbaud Feb 5, 2024 to Ashok Kumar

Hello @Ashok Kumar 
this SQL query is very powerful, but I'm not able to make it work neither with simple TOP, nor with %VID in order to get a pagination.

SELECT top 5
         JSON_ARRAYAGG(
          JSON_OBJECT(
             'Name':name
             ,'Age':age
             ,'DOB':to_char(dob,'Day DD Month YYYY')
            )
         ) 
         FROM sample.person
SQL
SQL
SELECT *
   FROM (SELECT
         JSON_ARRAYAGG(
          JSON_OBJECT(
            'Name':name
            ,'Age':age
            ,'DOB':to_char(dob,'Day DD Month YYYY')
          )
        ) 
         FROM sample.person)
WHERE %VID BETWEEN 1 AND 5
SQL
SQL

in both cases, I get the full table with all its records.

0 0
Vitaliy Serdtsev Feb 7, 2024 to Sylvain Guilbaud 
SELECT JSON_ARRAYAGG(json_obj)
  FROM (SELECT TOP 5
            JSON_OBJECT(
              'Name':name
              ,'Age':age
              ,'DOB':to_char(dob,'Day DD Month YYYY')
            ) json_obj
           FROM sample.person
       )
SELECT JSON_ARRAYAGG(json_obj)
  FROM (SELECT JSON_OBJECT(
                'Name':name
                ,'Age':age
                ,'DOB':to_char(dob,'Day DD Month YYYY')
                ) json_obj
       FROM sample.person
       )
  WHERE %VID BETWEEN 1 AND 5
1 1