Unnesting(?) a json string into columns and rows and duplicating down the order ids

MaxYouOut 0
Viewed 21

How do I extract the "product_id" into another column and row? I want it to look like the expected output screenshot. I'm not very experienced in json related functions so I tried using a json_extract_scalar but I think that doesn't deal with arrays?

Item Cell I want to extract into multiple cells

[{"product_id":"18741173","quantity":25,"price":7.9900000000000002,"price_raw":7.9900000000000002},
{"product_id":"17090192","quantity":3,"price":6.9900000000000002,"price_raw":6.9900000000000002},
{"product_id":"16152704","quantity":3,"price":6.9900000000000002,"price_raw":6.9900000000000002}]

Example data with order ID and purchase details in "items" desired output

google-bigquery sql
1 Answers

Use below

select orderId, json_value(item, '$.product_id') product_id,
  round(cast(json_value(item, '$.price_raw') as float64), 2) price
from your_table, unnest(json_query_array(items)) item

if applied to sample data in your question - output is

enter image description here

Mikhail Berlyant0
Related