Casting Datatypes

You can cast one data type to another using the cast expression.

In JSON, casting is particularly useful for timestamp information because JSON has no equivalent to the Oracle NoSQL Database Timestamp data type. Instead, the timestamp information is carried in a JSON object as a string. To work with it as a Timestamp, use cast.

In Working with Timestamps we showed how to work with the timestamp data type. In this case, what you do is no different except you must cast both sides of the expression. Also, because the left side of the expression is a sequence, you must specify a type quantifier (* in this case):

sql-> SELECT id, 
             j.person.firstname, j.person.lastname, j.person.lastLogin 
             FROM JSONPersons j 
             WHERE CAST(j.person.lastLogin AS TIMESTAMP*) > 
             CAST("2016-11-01" AS TIMESTAMP) AND 
             CAST(j.person.lastLogin AS TIMESTAMP*) < 
             CAST("2016-11-30" AS TIMESTAMP);
 +----+------------+--------------+----------------------------+
 | id | firstname  |   lastname   |       lastLogin            |
 +----+------------+--------------+----------------------------+
 |  3 | John       | Morgan       | 2016-11-29T08:21:35.4971   |
 +----+------------+--------------+----------------------------+
 |  2 | John       | Anderson     | 2016-11-28T13:01:11.2088   |
 +----+------------+--------------+----------------------------+
 |  5 | Dana       | Scully       | 2016-11-08T09:16:46.3929   |
 +----+------------+--------------+----------------------------+

3 rows returned 

As another example, you can cast to an integer and then operate on that number:

sql-> SELECT id, j.person.mynumber, 
             CAST(j.person.mynumber as integer) * 10 AS TenTimes 
             FROM JSONPersons j WHERE EXISTS j.person.mynumber;
 +----+---------------------+----------+
 | id |      mynumber       | TenTimes |
 +----+---------------------+----------+
 |  7 | 5                   |       50 |
 +----+---------------------+----------+
 |  6 | 5                   |       50 |
 +----+---------------------+----------+ 

If you want to operate on just the row that contains the number as a string, use IS OF TYPE:

sql-> SELECT id, j.person.mynumber, 
             CAST(j.person.mynumber as integer) * 10 AS TenTimes 
             FROM JSONPersons j WHERE EXISTS j.person.mynumber
             AND j.person.mynumber IS OF TYPE (string);
 +----+---------------------+----------+
 | id |      mynumber       | TenTimes |
 +----+---------------------+----------+
 |  7 | 5                   |       50 |
 +----+---------------------+----------+