Snowflake Documentation Home

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;

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;

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;

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.

Related EWIs

  1. SSC-EWI-0073: Pending Functional Equivalence Review

PreviousNCLOB Data typeNextExtended Data Types

Last updated