时间戳

首页 > 解决方案 > Snowlfake JSON 键值更改，需要 SQL

snowflake-cloud-data-platform - Snowlfake JSON 键值更改，需要 SQL

问题描述

如何将键更改为其他名称？从country到COUNTRY

ID RECORD 2 { "country": "England", "id": "100200", "status": "morestatus" } 3 { "country": "AMERICA", "id": "100300", "status": "morestatus" } 1 { "country": "UK", "id": "100100", "status": "somestatus" } ID RECORD 2 { "COUNTRY": "England", "id": "100200", "status": "morestatus" } 3 { "COUNTRY": "AMERICA", "id": "100300", "status": "morestatus" } 1 { "COUNTRY": "UK", "id": "100100", "status": "somestatus" }

我试过了，但似乎这适用于“国家”的价值观，并且不能将国家更改为 COUNTRY

UPDATE "KAFKA_DB"."KAFKA_SCHEMA"."TARGET" T SET T.RECORD =OBJECT_INSERT(T.RECORD:'country','COUNTRY', TRUE) WHERE RECORD:"country" = 'country';

JSON 数据键值更改

标签： snowflake-cloud-data-platform

解决方案

您可以嵌套 OBJECT_DELETE 和 OBJECT_INSERT 以添加具有旧值的键并删除该键。

create temp table t1 as select parse_json('{ "country": "England", "id": "100200", "status": "morestatus" }') as V;

select * from t1;

select object_delete(object_insert(v, 'COUNTRY', v:country), 'country') from t1;

要保留更改，只需执行更新：

update t1 set v = object_delete(object_insert(v, 'COUNTRY', v:country), 'country');

推荐阅读