CODEWITHSUNDEEP
google-bigquery
Kenzzz
Kenzzz

Reputation: 85

Bigquery: explode SPLIT() output rows into multiple columns

I have a long string in one column and need to explode it in multiple rows and then split into multiple columns. The data looks like: ((a:10,b:20,c:test1)(a:40,b:50,c:test2)(a:60,b:70,c:test3)). When I applied split and regexp_replace I got result like

Select SPLIT(REGEXP_REPLACE(REGEXP_REPLACE(message, r'))',''), r'((','') ,')(') as msg FROM [mydataset.mytable]

Output: 

msg

a:10,b:20,c:test1

a:40,b:50,c:test2

a:60,b:70,c:test3



What I am looking for is:

a     b     c

10    20    test1

40    50    test2

60    70    test3
I have used split again to split rows by (,) but it gives me only one row rather than 3. Really appreciate your help.

Upvotes: 1

Views: 11450

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33745

Here's an alternate solution using standard SQL (uncheck the "Use Legacy SQL" box under "Show Options") that is still relatively verbose but requires less text manipulation:

WITH MyTable AS (
  SELECT messages
  FROM UNNEST(['((a:10,b:20,c:test1)(a:40,b:50,c:test2)(a:60,b:70,c:test3))',
               '((a:12,b:22,c:test4)(a:42,b:52,c:test5)(a:62,b:72,c:test6))'])
    AS messages)
SELECT
  (SELECT value FROM UNNEST(message_parts) WHERE name = 'a') AS a,
  (SELECT value FROM UNNEST(message_parts) WHERE name = 'b') AS b,
  (SELECT value FROM UNNEST(message_parts) WHERE name = 'c') AS c
FROM (
  SELECT ARRAY(SELECT AS STRUCT
                 SPLIT(part, ':')[OFFSET(0)] AS name,
                 SPLIT(part, ':')[OFFSET(1)] AS value
               FROM UNNEST(SPLIT(message, ',')) AS part) AS message_parts
  FROM (SELECT message FROM MyTable,
          UNNEST(REGEXP_EXTRACT_ALL(messages, r'\(([^\(\)]+)\)')) AS message)
);

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Try below example 

SELECT
  MIN(CASE WHEN name = 'a' THEN value END) AS a,
  MIN(CASE WHEN name = 'b' THEN value END) AS b,
  MIN(CASE WHEN name = 'c' THEN value END) AS c
FROM (
  SELECT
    message, msg, 
    REGEXP_EXTRACT(pair, r'(\w*):') AS name, 
    REGEXP_EXTRACT(pair, r':(\w*)') AS value
  FROM (
    SELECT message, msg, 
      SPLIT(msg) AS pair
    FROM (
      SELECT message, 
        SPLIT(REPLACE(REPLACE(message, '))',''), '((','') ,')(') AS msg
      FROM 
        (SELECT '((a:10,b:20,c:test1)(a:40,b:50,c:test2)(a:60,b:70,c:test3))' AS message),
        (SELECT '((a:12,b:22,c:test4)(a:42,b:52,c:test5)(a:62,b:72,c:test6))' AS message),
    )
  )
) 
GROUP BY message, msg

Upvotes: 2

Related Questions