将字符串转换为数组SQL/Snowflake?dbt

Question

我有以下列，每行有一个字符串：

country=US&lan=EN&零售商=nike&零售商=addidas&零售商=彪马

我想提取国家，局域网和零售商，所以最后我会有：

国家
US	EN	[nike，addidas puma]

单向使用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
)

给出：

零售商

COUNTRY	LAN
US	EN	["耐克"、"阿迪达斯"、"彪马"]

这些看起来像url参数。我会尝试dbt-util的get_url_parameter宏(请参阅文档(

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