在BigQuery中如何访问含唯一属性名的JSON内部属性？

你的JSON数据结构标准化后大致如下：

{
    "data": {       
        "menuMapping": {},
        "menuStructures": {},
        "menus": {
            "fc24304a-3c6f-43e3-b166-75ad588cc760": {
                "createdAt": "2021-02-06T10:59:40.609Z",
                "displayOptions": {},
                "currencyCode": "USD",
                "disableItemInstructions": false,
                "isMenuV2": true
            },
            "entities": {
                "combinationsMap": null,
                "customizationsMap": {}
            }
        }
    }
}

其中menus下的属性名是全局唯一的UUID，无法直接通过固定名称访问其内部属性，以下是两种处理思路：

无预设键值时的通用解法 #

如果没有额外存储这个唯一属性名的字段，可以用BigQuery的JSON函数动态提取：

• 方法一：通过JSON_KEYS获取唯一键后拼接路径

SELECT
  JSON_VALUE(
    JSON_QUERY(tablename.data.menus, CONCAT('$."', JSON_KEYS(tablename.data.menus)[OFFSET(0)], '"')),
    '$.createdAt'
  ) AS createdAt
FROM your_table

• 方法二：用UNNEST展开键值对

SELECT
  JSON_VALUE(menu_item, '$.createdAt') AS createdAt
FROM your_table,
UNNEST(JSON_QUERY_ARRAY(tablename.data.menus)) AS menu_item

结合你的发现优化查询 #

你提到数据集里另有一个键值对存储了这些唯一UUID，假设存储该UUID的字段名为menu_id，直接拼接路径即可：

SELECT
  JSON_VALUE(tablename.data.menus, CONCAT('$."', menu_id, '"."createdAt"')) AS createdAt
FROM your_table

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