Links

MSCEWI2026

Json fields are inserted in an ascending ordering

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
Result
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;
id|Store |
--+---------------------------------------------------------------------+
1|{ "firstName" : "Joaquin", "lastName" : "Jimenez", "age" : 22}|

Output:

Json Insert
Result
/*** 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;
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.
  • For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.