How can I apply a function to each element of a array column?

user_90580 0
Viewed 1135

I have a dataset were a column in with an ARRAY of OBJECTs like this:

ID   TAGS
1    {"tags": [{"tag": "a"}, {"tag": "b"}]}
2    {"tags": [{"tag": "c"}, {"tag": "d"}]}

I want to extract the tag field of each element of the array, so the end result would be:

ID   TAGS
1    ["a","b"]
2    ["c","d"]

Assuming the following table t1:

CREATE OR REPLACE TEMPORARY TABLE t1 AS (
      select 1 as ID , PARSE_JSON('{"tags": [{"tag":"a"}, {"tag":"b"}]}') AS PAYLOAD
    UNION ALL
    select 2, PARSE_JSON('{"tags": [{"tag":"c"}, {"tag":"d"}]}')

);
snowflake-cloud-data-platform
2 Answers

One possible solution is to create a javascript function and use the javascript .map() to apply a function to each element of the array: 

create or replace function extract_tags(a array)
  returns array
  language javascript
  strict
  as '

  return A.map(function(d) {return d.tag});
  ';

SELECT ID, EXTRACT_TAGS(PAYLOAD:tags) AS tags from t1;

this gives the desired result: 

ID  TAGS
1   [    "a",    "b"  ]
2   [    "c",    "d"  ]
user_905800

A pure SQL approach would be to combine LATERAL FLATTEN and ARRAY_AGG like this:

with t2 as (
    select ID, t2.value:tag as tag
    from t1, LATERAL FLATTEN(input => payload:tags) t2
)
select t2.id, ARRAY_AGG(t2.tag) as tags from t2
group by ID 
order by ID ASC;

t2 itself will become: 

ID  TAG
1   "a"
1   "b"
2   "c"
2   "d"

and after the GROUP BY ID it becomes: 

ID  TAGS
1   [    "a",    "b"  ]
2   [    "c",    "d"  ]
user_905800
Related