JSON Data Type
We have some JSON data. We can create a column that would accept string data type and put this JSON inside. If we want more functionality, we can use special JSON data type. In that case only valid JSON could be placed into that column.
We create a JSON column like this. Data type could be JSON, or JSON(100) if we want to limit length.
CREATE TABLE JsonTable (JsonColumn JSON );We will place our JSON sample in that table.
INSERT INTO JsonTable ( JsonColumn ) VALUES (
'{ "store": { "book": [ { "Title": "Door" , "price": 8.95 }
, { "Title": "Sword" , "price": 12.99 } ]
, "bicycle": { "color": "red", "price": 19.95 } }
, "employee": { "Name": "Runako" } }' )We can read from JSON column like from any column with "SELECT * FROM JsonTable;", but if we want to get only some part of JSON code, we have to use JSONpath expressions. JSONpath expressions can define what part of JSON code we want to read. JSONpath expression should be placed as argument in JSON.FILTER function. Result will always be returned inside square brackets. Result is also of JSON data type. JSONpath expressions are case sensitive.
SELECT JSON.FILTER( JsonColumn, '$') FROM JsonTable; | $ represents root of a JSON. |
SELECT JSON.FILTER( JsonColumn, '$.store.bicycle.color') FROM JsonTable; | We read the direct child of the hierarchical path. |
SELECT JSON.FILTER( JsonColumn, '$..Title') FROM JsonTable; | This will return all elements that are of type "title" regardless of where they are in the hierarchy. We can limit this type of filter to the interior of the "store" only. |
SELECT JSON.FILTER( JsonColumn, 0 ) FROM JsonTable; | This would return the first element of the JSON. It will return a value inside the "store". This time we don't use JSONpath, but a number. |
SELECT JSON.FILTER( JsonColumn, '$.store.book.[0]') FROM JsonTable; | We will get the first element in the array. |
SELECT JSON.FILTER( JsonColumn, '$.store.book[0,1]') FROM JsonTable;[{ "book": [ { "Title": "Door" , "price": 8.95 } | We can read the first two elements in the array. Notice that we don't have a period after "book". |
SELECT JSON.FILTER( JsonColumn, '$.store.book.[0].Title') FROM JsonTable; | We can read the "Title" member from the first element in the array. |
SELECT JSON.FILTER( JsonColumn, '$.store.book.[0].*') FROM JsonTable; | We will only get the values from the first element in the array. |
SELECT JSON.FILTER( JsonColumn, '$.store.book.[*]') FROM JsonTable; [{ "Title": "Door" , "price": 8.95 },{ "Title": "Sword" , "price": 12.99 }] | We will read all of the elements in the array. |
There are other functions for use with JSON, that do not use JSONpath.
SELECT JSON.TEXT(JsonColumn, '_|_' ) FROM JsonTable; | This will collect all the values from our JSON. The values will be concatenated with a separator string between them. If we don't provide second argument, the separator will be a space character. |
SELECT JSON.NUMBER( JSON '{"n":4}'); 4 | All of these functions will return the number four as DOUBLE. There is also a function that accepts the same arguments but will return an INTEGER, JSON."INTEGER"(). Both functions will return NULL if they fail. |
SELECT JSON.ISOBJECT( JSON '[4]' ); false | This function will return "true" if it is a JSON object. If it is a JSON array, then it will return false. This function also accepts STRING data type. SELECT JSON.ISOBJECT( '{"n":4}'); |
SELECT JSON.ISARRAY( JSON '[4]' ); true | This function returns true when JSON is an array (it also accepts a STRING data type). |
| SELECT JSON.ISVALID( JsonColumn ) FROM JsonTable; true | This function will return "true" if the given JSON code is valid. |
| SELECT JSON.LENGTH( JsonColumn ) FROM JsonTable; 2 | This will count the top elements in our JSON. |
| SELECT JSON.KEYARRAY( JsonColumn ) FROM JsonTable; ["store","employee"] | This function will return all the top keys from the JSON sample. The top-level JSON must be an object, otherwise an error will be returned. |
| SELECT JSON.VALUEARRAY( JsonColumn ) FROM JsonTable; [{ "book": [ { "Title": "Door" , "price": 8.95 } , { "Title": "Sword" , "price": 12.99 } ] , "bicycle": { "color": "red", "price": 19.95 } } ,{ "Name": "Runako" }] | This function will return the values for the top keys |
There is another JSON function that can create JSON code from DOUBLE or CLOB columns. The returned value will be of data type CLOB.
| NumberCol | StringCol |
| 1 | One |
| 2 | Two |
| 3 | Three |
CREATE TABLE NumberString ( NumberCol DOUBLE, StringCol CLOB )
INSERT INTO NumberString ( NumberCol, StringCol ) VALUES ( 1, 'one' ), ( 2, 'two' ), ( 3, 'three' )In both cases the functions work in the same way. They concatenate all the values into a JSON string.
SELECT JSON.TOJSONARRAY( NumberCol ) NumberClob, JSON.TOJSONARRAY( StringCol ) StringClob FROM NumberString; |
UUID Data Type
A UUID is a unique value created by an algorithm. The UUID looks like this "65950c76-a2f6-4543-660a-b849cf5f2453". The UUID is represented as 32 hexadecimal digits separated into 5 groups of digits. The first group has 8 digits, then we have three groups of 4 digits, and finally there is one group of 12 digits. Together they define a really big number. Every time we call a function to create a new UUID, it will likely create a new value that was never used, anywhere in the world. Such values are used as a synthetic key that is globally unique.
We can create a new UUID value with this function:
SELECT SYS.UUID() AS UUID; 65950c76-a2f6-4543-660a-b849cf5f2453If we want to check whether some value is a valid UUID we can use function SYS.ISAUUID( string ). This function will return TRUE if we have a string formatted like 65950c76–a2f6–4543–660a–b849cf5f2453 or like 65950c76a2f64543660ab849cf5f2453, without hyphens.
SELECT SYS.ISAUUID('e31960fb-dc8b-452d-ab30-b342723e7565'); trueWe can transform a string into a UUID data type using the CAST or CONVERT functions, or we can use the UUID unary operator:
select cast( '26d7a80b-7538-4682-a49a-9d0f9676b765' as uuid) as uuid_val;
select convert('83886744-d558-4e41-a361-a40b2765455b', uuid) as uuid_val;
select uuid'AC6E4E8C-81B5-41B5-82DE-9C837C23B40A' as uuid_val;