Flatten XML array to a single row in Snowflake

Question

I have thousands of XML files which each have an array of key/value pairs. Like this:


  
    key_name_1
    value_1
  
  
    key_name_2
    value_2

I need to be able to create a query that produces a single-row response for each XML file containing each of those values in a separated column named by the key. With MySQL, I was able to use extractvalue with XPath ('extras/extra[key="key_name_1"/value') for this, but with Snowflake I am not able to find a solution for this.

I have tried lateral flatten and then picking up the value from THIS array, but I haven't succeeded. It is probably something simple, but I am not able to find the solution.

---Update---

I could always use something like this:

XMLGET(XMLGET("xml_data", 'extras'):"$"[0], 'value'):"$"

Unfortunately, all keys aren't always available making this useless.

Can it be that I have to do a UDF for this?

--- Update 2 ---

Got it working following way:

WITH Extra_1 AS (
    SELECT
        "id" as "id",
        XMLGET(extra.value, 'value'):"$" AS "value"
    FROM table,
        LATERAL FLATTEN(XMLGET("xml_data", 'extras'):"$") extra
    WHERE XMLGET(extra.value, 'key'):"$" = 'key_name_1'
),
WITH Extra_2 AS (
    SELECT
        "id" AS "id",
        XMLGET(extra.value, 'value'):"$" AS "value"
    FROM table,
        LATERAL FLATTEN(XMLGET("xml_data", 'extras'):"$") extra
    WHERE XMLGET(extra.value, 'key'):"$" = 'key_name_2'
)
SELECT
    "id",
    Extra_1."value" AS "key_name_1",
    Extra_2."value" AS "key_name_2",
FROM table
    JOIN Extra_1 ON Extra_1."id" = table."id";
    JOIN Extra_2 ON Extra_2."id" = table."id";

Hopefully, someone has a simpler way to do this.

This is how easily it can be done in MySQL:

SELECT
    id,
    extractvalue( xml_data,'/extras/extra[key="key_name_1"]/value') AS key_name_1,
    extractvalue( xml_data,'/extras/extra[key="key_name_2"]/value') AS key_name_2
FROM table;

user_5070879 · Answer

Using FLATTEN and XMLGET:

CREATE OR REPLACE TABLE t
AS
SELECT 1 AS id, PARSE_XML('
  
    key_name_1
    value_1
  
  
    key_name_2
    value_2
  
') AS col_xml
UNION ALL
SELECT 2 AS id, PARSE_XML('
  
    key_name_3
    value_3
  
  
    key_name_4
    value_4
  
 
    key_name_5
    value_5
  
') AS col;

Query:

SELECT id,  
    XMLGET(s.VALUE,  'key'):"$"::STRING AS k
   ,XMLGET(s.VALUE,  'value'):"$"::STRING AS val
FROM t
,LATERAL FLATTEN(INPUT => t.col_xml:"$") s
WHERE GET(s.value, '@')::STRING='extra';

Output:

Single row:

SELECT id 
    ,GET(XMLGET(t.col_xml,  'extra', 0), '$')[0]:"$"::STRING AS key1
    ,GET(XMLGET(t.col_xml,  'extra', 0), '$')[1]:"$"::STRING AS value1
    ,GET(XMLGET(t.col_xml,  'extra', 1), '$')[0]:"$"::STRING AS key2
    ,GET(XMLGET(t.col_xml,  'extra', 1), '$')[1]:"$"::STRING AS value2
    ,GET(XMLGET(t.col_xml,  'extra', 2), '$')[0]:"$"::STRING AS key2
    ,GET(XMLGET(t.col_xml,  'extra', 2), '$')[1]:"$"::STRING AS value2
FROM t;

user_43992 · Answer

If you have only 2 values you are wanting to pull you can merge Lukasz's answer with a CASE:

WITH fake_data AS (
    SELECT column1 as id,
        PARSE_XML(column2) as col_xml
    FROM VALUES
        (1, 'key_name_1value_1
                     key_name_2value_2
             '),
        (2, 'key_name_1value_3
             '),
        (3, 'key_name_2value_4
             ')
 )
 SELECT *
    ,case 
        when GET(XMLGET(t.col_xml,  'extra', 0), '$')[0]:"$" = 'key_name_1' then GET(XMLGET(t.col_xml,  'extra', 0), '$')[1]:"$"::STRING
        when GET(XMLGET(t.col_xml,  'extra', 1), '$')[0]:"$" = 'key_name_1' then GET(XMLGET(t.col_xml,  'extra', 1), '$')[1]:"$"::STRING
    end as key_name_1
    ,case 
        when GET(XMLGET(t.col_xml,  'extra', 0), '$')[0]:"$" = 'key_name_2' then GET(XMLGET(t.col_xml,  'extra', 0), '$')[1]:"$"::STRING
        when GET(XMLGET(t.col_xml,  'extra', 1), '$')[0]:"$" = 'key_name_2' then GET(XMLGET(t.col_xml,  'extra', 1), '$')[1]:"$"::STRING
    end as key_name_2      
FROM fake_data as t;

which gives:

ID	COL_XML	KEY_NAME_1	KEY_NAME_2
1	key_name_1 value_1 key_name_2 value_2	value_1	value_2
2	key_name_1 value_3	value_3
3	key_name_2 value_4		value_4

user_43992 · Answer

If you have "large amount of columns" then FlATTEN the data then pivot it (or some other GROUP BY)

WITH fake_data AS (
    SELECT column1 as id,
        PARSE_XML(column2) as col_xml
    FROM VALUES
        (1, 'key_name_1value_1
                     key_name_2value_2
             '),
        (2, 'key_name_1value_3
             '),
        (3, 'key_name_2value_4
             ')
 )
 SELECT *
 FROM (
     SELECT 
        t.id
        --,f.seq
        ,xmlget(f.value,'key'):"$"::text as key
        ,xmlget(f.value,'value'):"$"::text as value
    FROM fake_data as t
      ,table(flatten(input=>to_array(t.col_xml:"$"))) f
 ) d pivot(min(value) for key in ('key_name_1','key_name_2')) as p
 ORDER BY id;

gives:

ID	COL_XML	KEY_NAME_1	KEY_NAME_2
1	key_name_1 value_1 key_name_2 value_2	value_1	value_2
2	key_name_1 value_3	value_3
3	key_name_2 value_4		value_4

Things to note with this solution the input to FLATTEN is forced to an array so that the XML object that have only one child are not thought of as a object, but an array, so the FLATTEN process is consistant.

Flatten XML array to a single row in Snowflake

3 Answers