主页 / coding / 问题 / 77046356
Accepted
Kar
Asked: 2023-09-06 00:50:29 +0800 CST

Snowflake：JSON 中最独特的行字段

  • 772

要求：要获取在所有行中具有唯一值的 single_address_object 示例如下数据，国家和地区在所有行中都是通用的

     id , address
     ----------------
    111 , {   "City": "Garner",   "Country": "US",   "County": "Hancock",   "Line1": "490 W 8th St",   "PostalCode": "50438",   "Region": "TX" }
    111 , {   "City": "Colleyville",   "Country": "US",   "County": "Tarrant",   "Line1": "1002 Saddlebrook Dr",   "PostalCode": "76034",   "Region": "TX" }
    111 , {   "City": "Dublin",   "Country": "US",   "County": "Franklin",   "Line1": "545 Metro Pl S Ste 100",   "PostalCode": "43017",   "Region": "TX" }

输出

需要如下新列

    single_address_object
    ----------------------
    {"Country:"US", "Region":"TX"}
snowflake-cloud-data-platform

1 个回答

  1. Best Answer

    检查以下内容是否适合您。如果您希望是动态的，那么您需要编写存储过程来解析列名称并使用动态列名称构建类似于下面的查询。

    select case when left(res,2)='{,' then '{'||right(res,len(res)-2) else res end res
from (
    select '{'
        ||
        
          ifnull(case when count(distinct city) = 1 then '"city":"'||max(city)||'"' end ,'')
        || ifnull(case when count(distinct Country) = 1 then ',"Country":"'||max(Country)||'"' end ,'')
        || ifnull(case when count(distinct County) = 1 then ',"County":"'||max(County)||'"' end ,'')
        || ifnull(case when count(distinct Line1) = 1 then ',"Line1":"'||max(Line1)||'"' end ,'')
        || ifnull(case when count(distinct PostalCode) = 1 then ',"PostalCode":"'||max(PostalCode)||'"' end ,'')
        || ifnull(case when count(distinct Region) = 1 then ',"Region":"'||max(Region)||'"' end ,'')
        ||'}'
         res
    from (
        select 
         ADDRESS:City :: varchar City
        ,ADDRESS:Country :: varchar Country
        ,ADDRESS:County :: varchar County
        ,ADDRESS:Line1 :: varchar Line1
        ,ADDRESS:PostalCode :: varchar PostalCode
        ,ADDRESS:Region :: varchar Region
        from q5
    )
)
;
    • 1

相关问题