如何在BigQuery中查询Firestore导出的数组对象获取商品销售数量

1. 修复Firestore导出BigQuery的schema配置 #

你之前的schema只声明了line_items为数组类型，没有指定数组内的对象结构，所以导出后BigQuery无法识别为对象数组，只会默认解析为字符串类型。请使用如下完整schema配置重新导出数据：

{
  "name": "line_items",
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "price": {"type": "integer"},
      "qyt": {"type": "integer"},
      "name": {"type": "string"}
    }
  }
}

注意：请确认Firestore中数量字段的拼写为qyt，如果是拼写错误实际为qty请同步修改schema中的字段名

2. BigQuery查询语句 #

场景1：已按照正确schema重新导出，line_items为对象数组类型 #

使用UNNEST方法打平数组即可提取字段：

SELECT
  item.name,
  item.qyt AS qty
FROM
  `你的项目ID.你的数据集名称.你的表名`,
  UNNEST(line_items) AS item

如果需要统计全量商品的累计售出数量，使用以下语句：

SELECT
  item.name,
  SUM(item.qyt) AS total_sold_qty
FROM
  `你的项目ID.你的数据集名称.你的表名`,
  UNNEST(line_items) AS item
GROUP BY item.name
ORDER BY total_sold_qty DESC

场景2：不想重新导出，直接处理当前存储为字符串的line_items #

先通过JSON解析函数把字符串转为数组再打平提取字段：

SELECT
  JSON_VALUE(item, '$.name') AS name,
  CAST(JSON_VALUE(item, '$.qyt') AS INT64) AS qty
FROM
  `你的项目ID.你的数据集名称.你的表名`,
  UNNEST(JSON_EXTRACT_ARRAY(line_items)) AS item

内容的提问来源于stack exchange，提问作者Louna Akkad