CODEWITHSUNDEEP
google-bigquery
James Harrington
James Harrington

Reputation: 103

Unnest array defined as string within a row in BigQuery

I have the following query

select 123 as user_id, "[\"A\",\"B\",\"C\"]" as category

which generates this data:

user_id       category
    123  ["A","B","C"]

What I would like to get from this data is:

user_id       category
    123              A
    123              B
    123              C

How could I do it?

Upvotes: 0

Views: 117

Answers (1)

Jaytiger
Jaytiger

Reputation: 12274

Use below.

WITH sample_data AS (
  select 123 as user_id, "[\"A\",\"B\",\"C\"]" as category
)
SELECT user_id, category 
  FROM sample_data, UNNEST(JSON_VALUE_ARRAY(category)) category;

Upvotes: 1

Related Questions