How to create an array by replacing values from another array?

Question

I am looking for a way to create an array from another array based on the values of the second array with value mapping.

E.g. Table A has columns id, some_array and I have some value mapping in mind, says, {'a': 1, 'b': 2, 'c': 3} I want to do a select to get the array of mapping results from some_array

So, if A is

| id | some_array |
|:---|:-----------|
| 1  | ['a', 'b'] |
| 2  | ['a', 'c'] |

I need a query to generate following

| id | parsed_array |
|:---|:-----------|
| 1  | [1, 2]     |
| 2  | [1, 3      |

How can I achieve this query?

Thanks!

Guru Stron · Accepted Answer · 2024-12-04 07:06:33Z

0

There are multiple ways of doing this, for example you can create a CTE containing mapping data, cross join it with the dataset and then use transform array function with MAP's element_at function (returns NULL if key is not present in the map) to map the data:

-- sample data
WITH dataset(id, some_array) as (
    values (1, array['a', 'b']),
    (2, array['a', 'c'])
),
-- query parts    
map_data(m) as (
    values (map(
                array['a', 'b', 'c'],
                array[1, 2, 3]
            ))
)

select id,
    transform(some_array, v -> element_at(m, v)) parsed_array 
from dataset
cross join map_data;

Output:

id	parsed_array
1	[1, 2]
2	[1, 3]

answered Dec 4, 2024 at 7:06

Guru Stron

150k11 gold badges186 silver badges231 bronze badges

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

Collectives™ on Stack Overflow

How to create an array by replacing values from another array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related