'Convert a string into an Array SQL/Snowflake?dbt

I have the following column with a string per row:

country=US&lan=EN&retailer=nike&retailer=addidas&retailer=puma

And I want to extract the country, lan and retailer, so at the end I will have:

country lan retailer
US EN [nike, addidas puma]

Any one knows how to do that in sql or dbt? Thanks

sqlsnowflake-cloud-data-platformdbt


Solution 1:[1]

One way using LISTAGG, SPLIT_PART, ARRAY_AGG

SELECT listagg(iff(key='country',value, null)) as country,
    listagg(iff(key='lan',value, null)) as lan,
    array_agg(iff(key='retailer',value, null)) as retailer
FROM (
    SELECT 
        SPLIT_PART(s.value,'=', 1) as key,
        SPLIT_PART(s.value,'=', 2) as value
    FROM TABLE(SPLIT_TO_TABLE('country=US&lan=EN&retailer=nike&retailer=addidas&retailer=puma','&')) s
)

gives:

COUNTRY LAN RETAILER
US EN [ "nike", "addidas", "puma" ]

but it depends how you want to handle multiple countries (same or different) etc etc. I suspect it will not end up what you want.

But if you want a similar pattern per row:

SELECT listagg(iff(key='country',value, null)) as country,
    listagg(iff(key='lan',value, null)) as lan,
    array_agg(iff(key='retailer',value, null)) as retailer
FROM (
    SELECT s.seq,
        SPLIT_PART(s.value,'=', 1) as key,
        SPLIT_PART(s.value,'=', 2) as value
    FROM (
        SELECT * FROM VALUES
         ('country=US&lan=EN&retailer=nike&retailer=addidas&retailer=puma'), 
         ('country=AU&lan=EN&retailer=nike&retailer=addidas&retailer=puma')
    ), TABLE(SPLIT_TO_TABLE(column1,'&')) s
) GROUP BY seq
ORDER BY SEQ

gives:

COUNTRY LAN RETAILER
US EN [ "nike", "addidas", "puma" ]
AU EN [ "nike", "addidas", "puma" ]

Solution 2:[2]

These look like url parameters. I would try dbt util's get_url_parameter macro (see docs)

select
    {{ dbt_utils.get_url_parameter(field='your_string_field', url_parameter='country') }} as country,
    {{ dbt_utils.get_url_parameter(field='your_string_field', url_parameter='lan') }} as lan,
    {{ dbt_utils.get_url_parameter(field='your_string_field', url_parameter='retailer') }} as retailer
from your_table

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 tconbeer

Related Questions