JSON_TYPE scalar function

The JSON_TYPE user-defined function returns the BSON type of the given element.

The schema is SYSTOOLS.

injson: An expression that returns a JSON document in binary format (extended BSON).
inelem: A character or graphic string constant that is not longer than 2048 bytes. inelem contains the path-qualified JSON field name to be searched for.
maxlength: maxlength specifies the maximum length of the return value of the binary field of this user-defined function. The default is 2048. If the length of the field value is greater than 2048, then this function returns a null value.

The result of the function is an integer.

The following table shows the BSON type that is returned for the corresponding data types or null value.

Table 1. BSON type returned
BSON type	Data type
1	64-bit binary floating point
2	UTF-8 string
3	Embedded document
4	Array
5	Binary data
6	Undefined – deprecated
7	ObjectID
8	Boolean
9	UTC datetime
10	Null value
11	Regular expression
12	DBPointer – Deprecated
13	Javascript code
14	Deprecated
15	JavaScript code with Scope
16	32-bit integer
17	Timestamp
18	64-bit integer
0xFF	Min key
0x7F	Max key

Example

Assume that table T1 contains following simple JSON document in a column C1 as a BSON format.

{name: “Mike”, age: 20}

The following query returns BSON type for ‘name’ and ‘age’ in JSON document.

SELECT SYSTOOLS.JSON_TYPE(C1, 'name', 2048) AS Name,   
       SYSTOOLS.JSON_TYPE(C1, 'age' , 2048) AS Age
FROM T1;;

  +---------------------------------+     
  |      NAME      |      AGE       |     
  +---------------------------------+     
1_|              2 |             16 |     
  +---------------------------------+

The value 2 is the BSON type for a string and 16 is the BSON type for an integer.