I have a table that has two columns: ID and arr_map_col. The arr_map_col is of array<map<string,string>> data type and I need to extract certain key and their values from it. This is the code to create the table:
select stack(5,
12,array(map('PA_Type', 'Sent', 'PA_Card', '115286', 'PA_Date', '202403', 'PA_Status', 'Done')),
14,array(map('PA_Type', 'Rec', 'PA_Card', '226254', 'PA_Date', '202401', 'PA_Status', 'Done')),
19,array(map('PA_Type', 'Fol', 'PA_Card', '152644', 'PA_Date', '202312', 'PA_Status', 'Pen')),
21, array(map('PA_Type', 'Sent', 'PA_Card', '556253', 'PA_Date', '202311', 'PA_Status', 'Comp'), map('PA_Type', 'Rec', 'PA_Card', '126353', 'PA_Date', '202402')),
11, array(map('PA_Type', 'Fol', 'PA_Card', '225483', 'PA_Date', '202311', 'PA_Status', 'Pen'), map('PA_Type', 'Sent', 'PA_Card', '353154', 'PA_Date', ''))
) as (id, arr_map_col)
For every ID, I need to extract 'PA_Type' and 'PA_card' from the 'arr_map_col' column. The output should be like below:
