MSCEWI2026

Json fields are inserted in an ascending ordering

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

Teradata display of JSONs is made in the same order as they were inserted but Snowflake displays the JSON values in alphabetically ascending order. The age value passes from being displayed last to first in the following example.

Code Example

Input:

Json Insert

CREATE TABLE BookStores(
    id INTEGER,
    Store JSON(2500) CHARACTER SET LATIN NOT NULL
);

INSERT INTO BookStores (id, Store) VALUES(1, '{
    "firstName" : "Joaquin",
    "lastName" : "Jimenez",
    "age" : 22
}');

select * from BookStores;

Result

id|Store                                                                |
--+---------------------------------------------------------------------+
 1|{  "firstName" : "Joaquin",  "lastName" : "Jimenez",  "age" : 22}|

Output:

Json Insert

/*** MSC-WARNING - MSCEWI2026 - JSON FIELDS ARE INSERTED IN AN ASCENDING ORDERING ***/
CREATE TABLE BookStores (
    id INTEGER,
    Store VARIANT NOT NULL
);

INSERT BookStores (id, Store) SELECT
1,
PARSE_JSON('{
    "firstName" : "Joaquin",
    "lastName" : "Jimenez",
    "age" : 22
}');

SELECT * FROM BookStores;

Result

id|Store                                                                |
--+---------------------------------------------------------------------+
 1|{   "age" : 22, "firstName" : "Joaquin",  "lastName" : "Jimenez"    }|

Recommendations

• JSON is data structures based on key:values, change your query so it does not depend on the order of the elements inside the JSON.

• If you need more support, you can email us at [email protected]