如何在Snowflake中使用UPDATE语句永久修改JSON列中的country字段名为COUNTRY

Hey there! Let's break down your approach and refine it for renaming the JSON field in Snowflake.

First, your original statement: Is it workable? #

Your core logic is on the right track, but it has a couple of small gaps to fix:

• The WHERE clause is missing an explicit check: T.RECORD:country IS NOT NULL. While Snowflake treats NULL in a WHERE condition as false (so it won't update records without the country field), writing this explicitly makes your intent clearer and avoids any edge cases.
• You can simplify the nested functions with a purpose-built Snowflake function, which is cleaner and more efficient.

Fixed Version of Your Original Approach #

If you want to stick with the object_insert + object_delete flow, here's the corrected statement:

UPDATE your_table_name T
SET T.RECORD = object_delete(object_insert(T.RECORD, 'COUNTRY', T.RECORD:country), 'country')
WHERE T.RECORD:country IS NOT NULL;

What this does:

1. object_insert adds the new uppercase COUNTRY field with the same value as the original country
2. object_delete removes the old lowercase country field
3. The WHERE clause ensures we only touch records that actually have the country field (no wasted updates)

Better Option: Use OBJECT_RENAME (Recommended) #

Snowflake has a built-in function just for this scenario: OBJECT_RENAME. It cuts out the nested function calls and does the rename in one step. Way cleaner!

UPDATE your_table_name T
SET T.RECORD = object_rename(T.RECORD, 'country', 'COUNTRY')
WHERE T.RECORD:country IS NOT NULL;

This function directly renames the country key to COUNTRY—no need to copy values first and then delete the old key. It's more efficient and easier to read.

How to Verify It Worked #

After running your update, double-check with this query to confirm the change took effect:

SELECT 
  RECORD,
  RECORD:COUNTRY::STRING AS uppercase_country,
  RECORD:country::STRING AS lowercase_country -- This should return NULL for all updated records
FROM your_table_name;

If the lowercase_country column is all NULL and uppercase_country shows the correct values, you're good to go!

内容的提问来源于stack exchange，提问作者Austin Jackson