BigQuery:从json对象数组中提取选定键的值

Question

我在bigQuery有个json

{
  "actors": {
    "stooges": [
      {
        "id": 1,
        "name": "Larry"
      },
      {
        "id": 2,
        "name": "Curly"
      },
      {
        "id": 3,
        "name": "Moe"
      }
    ]
  }
}

如何提取bigQuery中的每个bigQuery。

["Larry", "Curly", "Moe"]

下面是一些方便的bigQuery兼容语句(基于上面的json)。

-- Declaring a bigQuery variable 
DECLARE json_data JSON DEFAULT (SELECT PARSE_JSON('{ "actors": {"stooges": [{"id": 1,"name": "Larry"},{"id": 2,"name": "Curly"},{"id": 3,"name": "Moe"}]}}'));

-- Select statement. But this is no good for my use case since I don't want to specify element index ([0]) as the array size is dynamic
SELECT JSON_EXTRACT(json_data, '$.actors.stooges[0].name');

Answer 1

您可以尝试并考虑以下使用JSON_EXTRACT_ARRAY()的方法，然后取消嵌套，然后使用JSON_EXTRACT_SCALAR()提取值。有关文档 JSON函数的更多细节，您可以参考这个BigQuery。

select ARRAY(
  SELECT JSON_EXTRACT_SCALAR(json_array, '$.name') from UNNEST(JSON_EXTRACT_ARRAY(json_data,"$.actors.stooges"))json_array
)extracted_names

输出：

​