JSON Data Type

Description

Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views. Unlike relational data, JSON data can be stored in the database, indexed, and queried without any need for a schema that defines the data. (Oracle SQL Language Reference JSON Data Type).

The JSON data types are transformed to VARIANT in order to emulate the Oracle behavior.

JSON

Sample Source Patterns

JSON Data Type as a column in Create Table

Oracle

Query

CREATE TABLE json_table (
	json_column JSON
);

INSERT INTO json_table VALUES('{"id": 1, "content":"json content"}');
INSERT INTO json_table VALUES('{"stringdata": "this is a text","number": 1,"numberNeg": -1,"booleanT": true,"booleanGF": false,"nullvalue": null,"object": {"1": 1,"2": 2},"array": [1, 2, 3]}');
INSERT INTO json_table VALUES(JSON('{"id": 4}'));
 
SELECT  * FROM json_table;

Result

COL1                                                                                                                                                |
----------------------------------------------------------------------------------------------------------------------------------------------------+
{"id":1,"content":"json content"}                                                                                                                   |
{"stringdata":"this is a text","number":1,"numberNeg":-1,"booleanT":true,"booleanGF":false,"nullvalue":null,"object":{"1":1,"2":2},"array":[1,2,3]} |
{"id":4}                                                                                                                                            |

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.json_table_ (
json_column VARIANT /*** MSC-WARNING - MSCEWI1036 - JSON DATA TYPE CONVERTED TO VARIANT ***/);

INSERT INTO PUBLIC.json_table_ VALUES('{"id": 1, "content":"json content"}');

INSERT INTO PUBLIC.json_table_ VALUES('{"stringdata": "this is a text","number": 1,"numberNeg": -1,"booleanT": true,"booleanGF": false,"nullvalue": null,"object": {"1": 1,"2": 2},"array": [1, 2, 3]}');

INSERT INTO PUBLIC.json_table_ VALUES(/*** MSC-WARNING - MSCEWI1049 - JSON FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.JSON_UDF('JSON(\'{"id": 4}\')'));

SELECT  * FROM PUBLIC.json_table_;

JSON data insertions are not being correctly handled. Check the Recommendations section for workarounds.

Known Issues

1. JSON data insertions

JSON data insertions are not being correctly handled by SnowConvert.

2. JSON objects manipulation

The usages of JSON objects (columns, variables, or parameters) are not correctly converted by SnowConvert. Check the Recommendations section for workarounds.

Related EWIs

1. MSCEWI1036: Data type converted to another data type.

2. MSCEWI1049: Not recognized function.

Recommendations

1. JSON Data Type translation workaround

JSON datatype is translated to VARIANT, so the information can be formatted using the Snowflake PARSE_JSON function. This approach will allow you to store, query, and operate the JSON data in Snowflake using similar syntax as Oracle.

Oracle

Query

CREATE TABLE json_table (
	json_column JSON
);

INSERT INTO json_table VALUES('{"id": 1, "content":"json content"}');
INSERT INTO json_table VALUES('{"id": 2, "content": {"header": "header text one", "content": "content text one"}}');
INSERT INTO json_table VALUES('{"id": 3, "content": {"header": "header tex two", "content": "content text two"}}');

SELECT * FROM json_table;
SELECT 'ID: ' || jt.json_column.id, 'HEADER: ' || UPPER(jt.json_column.content.header) FROM json_table jt;

Result 1

|JSON_SERIALIZE(JSON_COLUMN)                                                 |
|----------------------------------------------------------------------------|
|{"id":1,"content":"json content"}                                           |
|{"id":2,"content":{"header":"header text one","content":"content text one"}}|
|{"id":3,"content":{"header":"header tex two","content":"content text two"}} |

Result 2

|'ID:'||JT.JSON_COLUMN.ID|'HEADER:'||UPPER(JT.JSON_COLUMN.CONTENT.HEADER)|
|------------------------|-----------------------------------------------|
|ID: 1                   |HEADER:                                        |
|ID: 2                   |HEADER: "HEADER TEXT ONE"                      |
|ID: 3                   |HEADER: "HEADER TEX TWO"                       |

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.json_table (
json_column VARIANT);

INSERT INTO PUBLIC.json_table SELECT PARSE_JSON('{"id": 1, "content":"json content"}');
INSERT INTO PUBLIC.json_table SELECT PARSE_JSON('{"id": 2, "content": {"header": "header text one", "content": "content text one"}}');
INSERT INTO PUBLIC.json_table SELECT PARSE_JSON('{"id": 3, "content": {"header": "header tex two", "content": "content text two"}}');

SELECT * FROM PUBLIC.json_table;
SELECT 'ID: ' || jt.json_column:id, 'HEADER: ' || UPPER(jt.json_column:content:header) FROM PUBLIC.json_table jt;

Result 1

|JSON_COLUMN                                                                                         |
|----------------------------------------------------------------------------------------------------|
|{  "content": "json content",  "id": 1}                                                          |
|{  "content": {    "content": "content text one",    "header": "header text one"  },  "id": 2}|
|{  "content": {    "content": "content text two",    "header": "header tex two"  },  "id": 3} |

Result 2

|'ID: ' || JT.JSON_COLUMN:ID|'HEADER: ' || UPPER(JT.JSON_COLUMN:CONTENT:HEADER)| |---------------------------|--------------------------------------------------| |ID: 1 | | |ID: 2 |HEADER: HEADER TEXT ONE | |ID: 3 |HEADER: HEADER TEX TWO ||'ID: ' || JT.JSON_COLUMN:ID|'HEADER: ' || UPPER(JT.JSON_COLUMN:CONTENT:HEADER)|
|---------------------------|--------------------------------------------------|
|ID: 1                      |                                                  |
|ID: 2                      |HEADER: HEADER TEXT ONE                           |
|ID: 3                      |HEADER: HEADER TEX TWO                            

You must use SELECT as the INSERT INTO argument instead of the VALUES clause to use the PARSE_JSON function.

Use the ':' instead of the '.' operator to access the JSON object properties. It allows several levels of nesting in both engines.