InterSystems SQL Reference
JSON_OBJECT
|
|
JSON_OBJECT(select-items [NULL ON NULL | ABSENT ON NULL])
select-items | A key:value pair or a comma-separated list of key:value pairs. A key is a user-specified literal string delimited with single quotes. A value can be a column name, an aggregate function, an arithmetic expression, a numeric or string literal, or the literal NULL. |
ABSENT ON NULL NULL ON NULL |
Optional A keyword phrase specifying how to represent NULL values in the returned JSON object. NULL ON NULL (the default) represents NULL (absent) data with the word null (not quoted). ABSENT ON NULL omits NULL data from the JSON object; it removes the key:value pair when value is NULL and does not leave a placeholder comma. This keyword phrase has no effect on empty string values. |
{ "key1" : "value1" , "key2" : "value2" , "key3" : "value3" }
SET myquery = 2 SET myquery(1) = "SELECT TOP 3 JSON_OBJECT('id':%ID,'name':Name,'birth':DOB," SET myquery(2) = "'age':Age,'state':Home_State) FROM Sample.Person" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() WHILE rset.%Next() {DO rset.%Print(" ^ ")} WRITE !,"Total row count=",rset.%ROWCOUNT
SET myquery = 2 SET myquery(1) = "SELECT TOP 3 JSON_OBJECT('lit':'Employee from','t':%TABLENAME," SET myquery(2) = "'name':Name,'num':SSN) FROM Sample.Employee" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() WHILE rset.%Next() {DO rset.%Print(" ^ ")} WRITE !,"Total row count=",rset.%ROWCOUNT
SET myquery = 2 SET myquery(1) = "SELECT JSON_OBJECT('name':Name,'colors':FavoriteColors) FROM Sample.Person" SET myquery(2) = " WHERE Name %STARTSWITH 'S'" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() WHILE rset.%Next() {DO rset.%Print(" ^ ")} WRITE !,"Total row count=",rset.%ROWCOUNT
SET myquery = 3 SET myquery(1) = "SELECT TOP 8 JSON_OBJECT('ODBCBday':DOB,'DispBday':%EXTERNAL(DOB))," SET myquery(2) = "JSON_OBJECT('ODBCcolors':FavoriteColors,'DispColors':%EXTERNAL(FavoriteColors)) " SET myquery(3) = "FROM Sample.Person" SET tStatement = ##class(%SQL.Statement).%New() SET tStatement.%SelectMode=1 WRITE "SelectMode is ODBC",! SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() IF rset.%SQLCODE=0 { WRITE !,"Executed query",! } ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)} DO rset.%Display() WRITE !,"End of data"
SET myquery = 2 SET myquery(1) = "SELECT TOP 3 JSON_OBJECT('e.t':E.%TABLENAME,'e.name':E.Name,'c.t':C.%TABLENAME," SET myquery(2) = "'c.name':C.Name) FROM Sample.Employee AS E,Sample.Company AS C" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() WHILE rset.%Next() {DO rset.%Print(" ^ ")} WRITE !,"Total row count=",rset.%ROWCOUNT
SET myquery = 2 SET myquery(1) = "SELECT Name,Home_City,Home_State FROM Sample.Person" SET myquery(2) = " WHERE JSON_OBJECT('name':Name,'city':Home_City,'state':Home_State) [ 'X'" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() WHILE rset.%Next() {DO rset.%Print(" ^ ")} WRITE !,"Total row count=",rset.%ROWCOUNT