snowflake-cloud-data-platform - 将 JSON 多个值解析为行

Question

要求：将 JSON 值解析为行

JSON：

    {
      "uid":"2EDA9DC1D4",
      "m_lg_loc": "ml_0_49_2965_12990434_1450,ml_0_49_2965_12991888_1450,ml_0_49_2965_12997254_682,ml_0_49_2965_12997940_453",
      "codec": "PMMMU,G726-32,PMMMA,A729a,tel",
      "trv_dev": "1,10,2",
        "geoipp": {
        "area_code": 703,
        "location": [
          -77.2223,
          38.94990000014
        ]
      }
    }

预期输出：

需要m_lg_loc将多个值放入行

ml_0_49_2965_12990434_1450
ml_0_49_2965_12991888_1450
ml_0_49_2965_12997254_682
ml_0_49_2965_12997940_453

同样对于

codec

    PMMMU
    G726-32
    PMMMA
    A729a 
    tel

location

-77.2223
38.94990000014

试过：

select JSON_EXTRACT_PATH_TEXT($1, uid) as uid
      ,JSON_EXTRACT_PATH_TEXT($1, 'm_lg_loc') as m_lg_loc
 from /path/abc.json (FILE_FORMAT=>JSON_FORMAT)

标签： snowflake-cloud-data-platform

Answer 1

你可以使用 SPLIT_TO_TABLE 来做你想做的事。这是一个例子：

create temporary table foo(v variant);

insert into foo select parse_json('    {
      "uid":"2EDA9DC1D4",
      "m_lg_loc": "ml_0_49_2965_12990434_1450,ml_0_49_2965_12991888_1450,ml_0_49_2965_12997254_682,ml_0_49_2965_12997940_453",
      "codec": "PMMMU,G726-32,PMMMA,A729a,tel",
      "trv_dev": "1,10,2",
        "geoipp": {
        "area_code": 703,
        "location": [
          -77.2223,
          38.94990000014
        ]
      }
    }');
    
--Parse the property as a string.
select v:m_lg_loc::string from foo;
--Split to table
select * from foo, table(SPLIT_TO_TABLE(v:m_lg_loc::string, ',')); 
--Get and alias the column(s) you want
select "VALUE" as MY_COLUMN  from foo, table(SPLIT_TO_TABLE(v:m_lg_loc::string, ','));