how to maintain order of elements using snowflake object_construct() function instead of sorting by the keys?

Question

Following snowflake query returns the JSON structure but output is sorted by the keys. How not to sort by the keys but retains the order? Is there any parameter setting that needs to be set?

select
object_construct
(
  'entity',  'XYZ',
  'allowed',  'Yes',
  'currency', 'USD',
  'statement_month','July, 2020'
 )

Output: --it sorts by the keys

{
  "allowed": "Yes",
  "currency": "USD",
  "entity": "XYZ",
  "statement_month": "July, 2020"
}

Expected Output: --same order as specified

{
  "entity": "XYZ",
  "allowed": "Yes",
  "currency": "USD",
  "statement_month": "July, 2020"
}

user_10017890 · Answer

JSON is an unordered collection of name and values. Order cannot be guaranteed in JSON.

The constructed object does not necessarily preserve the original order of the key-value pairs.

You can do it like as below

SELECT mytable:entity::string as entity,
mytable:allowed::string as allowed,
mytable:currency::string as currency,
mytable:statement_month::string as statement_month
from
(select
object_construct
(
  'entity',  'XYZ',
  'allowed',  'Yes',
  'currency', 'USD',
  'statement_month','July, 2020'
 ) mytable);

user_13859537 · Answer

Unfortunately, no

Usage notes: https://docs.snowflake.com/en/sql-reference/functions/object_construct.html#usage-notes

The constructed object does not necessarily preserve the original order of the key-value pairs.

same for PARSE_JSON Usage notes: https://docs.snowflake.com/en/sql-reference/functions/parse_json.html#usage-notes

The order of the key-value pairs in the string produced by TO_JSON is not predictable.

how to maintain order of elements using snowflake object_construct() function instead of sorting by the keys?

2 Answers