Bigquery - json_array 从嵌套数组中提取和计算元素

使用 BigQuery JSON_ARRAY 从嵌套数组中提取和计算元素

json
{
  "order_id": 123,
  "customer_name": "John Doe",
  "products": [
    {"product_name": "Product A", "price": 50},
    {"product_name": "Product B", "price": 30},
    {"product_name": "Product C", "price": 25}
  ]
}

sql
SELECT
  JSON_EXTRACT_ARRAY_ELEMENT_TEXT(order_data, '$.products[*].product_name') AS product_name,
  JSON_EXTRACT_ARRAY_ELEMENT(order_data, '$.products[*].price') AS price
FROM
  `your_project.your_dataset.your_table`

sql
SELECT
  order_id,
  customer_name,
  (SELECT SUM(CAST(product->>'price' AS INT64)) FROM UNNEST(products) AS product) AS total_amount
FROM
  `your_project.your_dataset.your_table`