将JSON多个值解析为行 [英] Parse JSON Multiple values into Rows

问题描述

要求：将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)

推荐答案

您可以使用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, ',')); 

这篇关于将JSON多个值解析为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！