IS JSON (SQL)

Determines if a data value is in JSON format.

Synopsis

scalar-expression IS [NOT] JSON [keyword]

Description

The IS JSON predicate determines if a data value is in JSON format.

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.

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.

IS JSON can be used wherever a predicate condition can be specified, as described in Overview of Predicates.

Arguments

scalar-expression

A scalar expression that is being checked for JSON formatting.

keyword

An optional argument. One of the following: VALUE, SCALAR, ARRAY, or OBJECT. The default is VALUE.

Examples

The following example determines if the predicate is a properly-formatted JSON string, either a JSON object or a JSON array:

SELECT TOP 5 Name FROM Sample.Person
    WHERE '{""name"":""Fred"",""spouse"":""Wilma""}' IS JSON

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:

  SET jarray=
  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()

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

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

The following example shows the behavior of the IS NOT JSON predicate:

SELECT Title,%OBJECT(Picture) AS PhotoOref FROM Sample.Employee
    WHERE Picture IS NOT JSON