How to pivot array build from REGEXP_EXTRACT_ALL

I'm collecting url with query parameters in a BigQuery table. I want to parse these urls and then pivot the table.

I found two queries that I want to merge.

This one to pivot my parsed url:

select id,          max(case when test.name='a' then test.score end) as a,         max(case when test.name='b' then test.score end) as b,         max(case when test.name='c' then test.score end) as c  from   (  select a.id, t  from `table` as a,  unnest(test) as t  )A group by id  

then I have this query to parse the url:

WITH examples AS (    SELECT 1   AS id,     '?foo=bar' AS query,     'simple'   AS description    UNION ALL SELECT 2, '?foo=bar&bar=baz', 'multiple params'    UNION ALL SELECT 3, '?foo[]=bar&foo[]=baz', 'arrays'    UNION ALL SELECT 4, '', 'no query'  )  SELECT     id,     query,    REGEXP_EXTRACT_ALL(query,r'(?:\?|&)((?:[^=]+)=(?:[^&]*))') as params,    REGEXP_EXTRACT_ALL(query,r'(?:\?|&)(?:([^=]+)=(?:[^&]*))') as keys,    REGEXP_EXTRACT_ALL(query,r'(?:\?|&)(?:(?:[^=]+)=([^&]*))') as values,    description  FROM examples  

I'm not sure to explain my issues. But I think that is because when I'm splitting my query parameters as separate columns It doesn't match with the format of the first query where I need to merge the key and values under the same column so I can unnest them correctly.

I hope that this make sense.

Thanks.

https://stackoverflow.com/questions/66926885/how-to-pivot-array-build-from-regexp-extract-all April 03, 2021 at 09:08AM