Skip to main content
Previous sectionNext section

IS JSON

Determines if a data value is in JSON format.

Synopsis

scalar-expression IS [NOT] JSON [keyword]

Arguments

Argument Description
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()
Copy code to clipboard

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()
Copy code to clipboard
  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()
Copy code to clipboard
  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()
Copy code to clipboard

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()
Copy code to clipboard

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