Caché SQL Reference
IS JSON
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Determines if a data value is in JSON format.
Synopsis
scalar-expression IS [NOT] JSON [keyword]
Arguments
scalar-expression A scalar expression that is being checked for JSON formatting.
keyword Optional — One of the following: VALUE, SCALAR, ARRAY, or OBJECT. The default is VALUE.
Description
The IS JSON predicate determines if a data value is in JSON format. The following example determines if the predicate is a properly-formatted JSON string, either a JSON object or a JSON array:
  ZNSPACE "SAMPLES"
  SET q1 = "SELECT TOP 5 Name FROM Sample.Person "
  SET q2 = "WHERE '{""name"":""Fred"",""spouse"":""Wilma""}' IS JSON"
  SET myquery = q1_q2
  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()
  DO rset.%Display()
 
IS JSON (with or without the optional VALUE keyword) returns true for any JSON array or JSON object. This includes an empty JSON array '[]' or an empty JSON object '{}'.
The VALUE keyword and the SCALAR keyword are synonyms.
IS JSON ARRAY returns true for a JSON array oref. IS JSON OBJECT returns true for a JSON object oref. This is shown in the following examples:
  ZNSPACE "SAMPLES"
  SET jarray=[1,2,3,5,8,13,21,34]
  WRITE "JSON array: ",jarray,!
  SET myquery = "SELECT TOP 5 Name FROM Sample.Person WHERE ? IS JSON ARRAY"
  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(jarray)
  DO rset.%Display()
 
  ZNSPACE "SAMPLES"
  SET jarray=[1,2,3,5,8,13,21,34]
  WRITE "JSON array: ",jarray,!
  SET myquery = "SELECT TOP 5 Name FROM Sample.Person WHERE ? IS JSON OBJECT"
  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(jarray)
  DO rset.%Display()
 
  ZNSPACE "SAMPLES"
  SET jobj={"name":"Fred","spouse":"Wilma"}
  WRITE "JSON object: ",jobj,!
  SET myquery = "SELECT TOP 5 Name FROM Sample.Person WHERE ? IS JSON OBJECT"
  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(jobj)
  DO rset.%Display()
 
For further details, refer to the ObjectScript SET command subsection JSON Object and JSON Array.
The IS NOT JSON predicate is one of the few predicates that can be used on a stream field in a WHERE clause. Its behavior is the same as IS NOT NULL. This is shown in the following example:
  ZNSPACE "SAMPLES"
  SET q1 = "SELECT Title,%OBJECT(Picture) AS PhotoOref FROM Sample.Employee "
  SET q2 = "WHERE Picture IS NOT JSON"
  SET myquery = q1_q2
  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()
  DO rset.%Display()
 
IS JSON can be used wherever a predicate condition can be specified, as described in the Overview of Predicates page of this manual.
See Also