如果有任何想法可以克服雪花横向平铺函数的模糊列，并在下面的逻辑中，非常感谢。 我试图通过从变量列中选择值，使用下面的查询来平铺嵌套的JSON数据，但是在横向平铺函数中得到了模糊的列名'VALUE'错误。谁能帮助我实现理想的输出。这里的问题是JSON的键名是 "value"，我不能用横向平铺的方式得到这个数据。希望的输出已经作为图片附在这个主题上。

JSON数据样本

 {"issues": [
"expand": "a,b,c,d",
"fields": {
"customfield_10000": null,
"customfield_10001": null,
"customfield_10002": [
    "id": "1234",
    "self": "xxx",
    "value": "Test"
"id": "123456",
"key": "K-123"
*select
    a.value:id::number as ISSUE_ID,
    a.value:key::varchar as ISSUE_KEY,
    b.value:id::varchar as ROOT_CAUSE_ID,
    **b.value:value::varchar as ROOT_CAUSE_VALUE**
    abc.table_variant,
    lateral flatten( input => payload_json:issues) as a,
    lateral flatten( input => a.value:fields.customfield_10002) as b;*
    
snowflake-cloud-data-platform
ambiguous
Maran
发布于 2020-08-08
2 个回答
demircioglu
发布于 2020-08-08
0 人赞同

b.value:"value"::varchar
    
demircioglu：
如果它对你有用，你是否介意将此标记为答案？
Varun Singhal
发布于 2020-08-08
0 人赞同

WITH CTE AS 
(select parse_json('{"issues": [
"expand": "a,b,c,d",
"fields": {
"customfield_10000": null,
"customfield_10001": null,
"customfield_10002": [
    "id": "1234",
    "self": "xxx",
    "value": "Test"
"id": "123456",
"key": "K-123"
 as col)
select 
a.value:id::number as ID, 
a.value:key::varchar as KEY, 
b.value:id::INT as customfield_10002,
b.value:value::varchar as customfield_10002_value