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

IN -> Oracle_01.sql

CREATE TABLE jsontable (
	json_column JSON
);

INSERT INTO jsontable VALUES('{"id": 1, "content":"json content"}');
INSERT INTO jsontable 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 jsontable VALUES(JSON('{"id": 4}'));
 
SELECT  * FROM jsontable;

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

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE jsontable (
	json_column VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

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

INSERT INTO jsontable
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 jsontable
VALUES(JSON('{"id": 4}') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'JSON' NODE ***/!!!);

SELECT  * FROM
	jsontable;

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

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

IN -> Oracle_02.sql

CREATE TABLE jsontable (
	json_column JSON
);

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

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

|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"}} |

|'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

OUT -> Oracle_02.sql

CREATE OR REPLACE TABLE jsontable (
	json_column VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

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

INSERT INTO jsontable
VALUES('{"id": 2, "content": {"header": "header text one", "content": "content text one"}}');

INSERT INTO jsontable
VALUES('{"id": 3, "content": {"header": "header tex two", "content": "content text two"}}');

SELECT * FROM
	jsontable;

SELECT 'ID: ' || NVL(jt.json_column.id :: STRING, ''), 'HEADER: ' || NVL(UPPER(jt.json_column.content.header) :: STRING, '') FROM
	jsontable jt;

|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} |

|'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