如何在Snowflake ARRAY类型字段中保留JSON对象键顺序

已通过Python建立与Snowflake的连接，代码如下：

import snowflake.connector
params = dict(
            user=<my_user>,
            password=<my_pass>,
            account=<my_acc>,
            warehouse=<my_wh>,
            database=<my_db>,
            schema=<my_schema>
)
ctx = snowflake.connector.connect(**params)

随后执行更新命令：

ctx.cursor().execute("update <my_wh>.<my_db>.<my_tb> set DICT_FIELD = [{'b': 1, 'c': 1, 'a': 1}] where ID = 'my_id';")

期望表中ARRAY类型的DICT_FIELD字段值为[{'b': 1, 'c': 1, 'a': 1}]，但实际得到的是[{'a': 1, 'b': 1, 'c': 1}]，需要保留发送时的JSON键顺序。

版本信息：

python=3.9.7
snowflake-connector-python[pandas]==3.0.3

Snowflake默认会对SQL中直接书写的对象字面量按键字母排序，要保留原始键顺序，需通过PARSE_JSON解析标准JSON字符串实现，以下是两种可行方法：

方法1：直接使用PARSE_JSON传入固定JSON字符串 #

修改更新语句，将数组内的字典写成符合JSON规范的字符串，通过PARSE_JSON解析后写入字段，即可保留键顺序：

update_sql = """
update <my_wh>.<my_db>.<my_tb> 
set DICT_FIELD = PARSE_JSON('[{"b": 1, "c": 1, "a": 1}]') 
where ID = 'my_id';
"""
ctx.cursor().execute(update_sql)

注意：JSON字符串需用单引号包裹，内部键名必须用双引号（符合JSON语法要求）。

方法2：动态生成有序JSON字符串（适合字典内容动态变化场景） #

如果字典是动态生成的，可借助Python的json.dumps函数，设置sort_keys=False生成保留键顺序的JSON字符串，再传入SQL：

import json
# 生成保留键顺序的JSON字符串
ordered_json = json.dumps([{'b': 1, 'c': 1, 'a': 1}], sort_keys=False)
update_sql = f"""
update <my_wh>.<my_db>.<my_tb> 
set DICT_FIELD = PARSE_JSON('{ordered_json}') 
where ID = 'my_id';
"""
ctx.cursor().execute(update_sql)

原理说明 #

Snowflake对SQL中直接书写的{'b':1,...}这类对象字面量，会默认按字母排序键；而PARSE_JSON解析JSON字符串时，会严格遵循字符串内的键顺序存储，从而实现保留原始顺序的需求。

内容的提问来源于stack exchange，提问作者Jose Rondon