Parse JSON Multiple values into Rows

Question

Requirement: Parse JSON values into rows

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
        ]
      }
    }

Expected Output:

Need m_lg_loc multiple values into rows

ml_0_49_2965_12990434_1450
ml_0_49_2965_12991888_1450
ml_0_49_2965_12997254_682
ml_0_49_2965_12997940_453

Similarly for

codec

    PMMMU
    G726-32
    PMMMA
    A729a 
    tel

location

-77.2223
38.94990000014

Tried:

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)

Greg Pavlik · Accepted Answer · 2020-07-31 16:10:17Z

1

You can use SPLIT_TO_TABLE to do what you want. Here's an example:

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, ','));

answered Jul 31, 2020 at 16:10

Greg Pavlik

11.3k2 gold badges14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kar Over a year ago

Thanks, @Greg Pavlik, I see you inserted into a temporary table, Is it possible to get directly from stage s3 path ..Below one got me SQL compilation error: ambiguous column name '$1'

select JSON_EXTRACT_PATH_TEXT($1, 'uid') as uid        ,JSON_EXTRACT_PATH_TEXT($1, 'media_leg_locations') as media_leg_locations        ,$1:media_leg_locations::string FROM @stage/path ( FILE_FORMAT=>JSON_FORMAT)a ,table(SPLIT_TO_TABLE($1,'media_leg_locations'::string, ','));

Greg Pavlik Over a year ago

You have three parameters in your split_to_table function. You'll want to parse the JSON in $1 and assign an alias for it. Almost all the time Snowflake will let you use an aliased column name later in the SQL.

Mike Walton · Accepted Answer · 2020-07-31 18:58:39Z

1

You can do this all in one step using a LATERAL FLATTEN:

WITH x AS (
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
        ]
      }
    }') as js)
SELECT js:uid::varchar, js:m_lg_loc::varchar, y.value::varchar
FROM x,
LATERAL FLATTEN(INPUT => split(x.js:m_lg_loc::varchar,',')) y;

answered Jul 31, 2020 at 18:58

Mike Walton

7,4592 gold badges14 silver badges26 bronze badges

Collectives™ on Stack Overflow

Parse JSON Multiple values into Rows

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related