CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athenapresto
rightjoin
rightjoin

Reputation: 77

Query to replace each value of a list

I want to write an athena query that results in a table that replaces each of the value in the list_ids array with the original_id.

Upvotes: 0

Views: 1382

Answers (1)

Naveen Mahadevuni
Naveen Mahadevuni

Reputation: 81

Thanks to one of my colleagues, a better, concise solution for the whole problem described by you including the struct column.

SELECT id, TRANSFORM(content_ids, id -> m[id]),
 TRANSFORM_VALUES(cast(t1.popular_ids as MAP(VARCHAR,ARRAY(VARCHAR))), (k,arr) ->  TRANSFORM(arr, v -> m[v]))
FROM t1, (SELECT MAP_AGG(content_id, original_id) as m FROM t2) t;

From table t2, we use MAP_AGG function to create a MAP 'm' of content_id and original_id. We then use TRANSFORM and lambda to transform array of content_ids to original_ids using this map, and use TRANSFORM_VALUES to transform the JSON first by CASTing it to MAP(VARCHAR,ARRAY(VARCHAR)), then lambda to transform each ARRAY in turn using TRANSFORM again.

Below is the first solution.

SELECT t1.id, ARRAY_AGG(t2.original_id) 
FROM t1 CROSS JOIN UNNEST(t1.content_ids) as t(content_id) 
JOIN t2 on t2.content_id = t.content_id 
GROUP BY t1.id;

|  id   |   _col1.    |
|-------|-------------|
| uid_1 | [O_1, O_2]  |

(1 row)

The first part of the query unnests the array.

SELECT id, content_id FROM t1 CROSS JOIN UNNEST(t1.content_ids) as t(content_id);
|  id   | content_id|
------- |-----------|
| uid_1 | 1         |
| uid_1 | 2         |

Now that content_ids array is unnested, the second part joins the unnested content_id in t1 with t2 on t2.content_id.

SELECT t1.id, t2.original_id FROM t1 CROSS JOIN UNNEST(t1.content_ids) as t(content_id) JOIN t2 on t2.content_id = t.content_id;
 
| id   | original_id   |
|-------|------------- |
| uid_1 | O_1          |
| uid_1 | O_2          |

Now original_id is derived for each id independently. To convert it back to the array, we do ARRAY_AGG operation on original_id grouping by id. That's the final query.

Upvotes: 1

Related Questions