如何使用 BigQuery 提取 JSON 对象中的所有键 [英] How to extract all the keys in a JSON object with BigQuery

问题描述

BigQuery 具有在实时交互式查询中解析 JSON 的功能:只需将 JSON 编码对象存储为字符串，并使用 JSON_EXTRACT_SCALAR 等函数实时查询.

BigQuery has facilities to parse JSON in real-time interactive queries: Just store the JSON encoded object as a string, and query in real time, with functions like JSON_EXTRACT_SCALAR.

但是，我找不到发现这些对象中所有键(属性)的方法.

However, I can't find a way to discover all the keys (properties) in these objects.

我可以为此使用 UDF 吗?

Can I use a UDF for this?

推荐答案

这里有一些使用 标准 SQL:

CREATE TEMP FUNCTION jsonObjectKeys(input STRING)
RETURNS Array<String>
LANGUAGE js AS """
  return Object.keys(JSON.parse(input));
""";
WITH keys AS (
  SELECT
    jsonObjectKeys(myColumn) AS keys
  FROM
    myProject.myTable
  WHERE myColumn IS NOT NULL
)
SELECT
  DISTINCT k
FROM keys
CROSS JOIN UNNEST(keys.keys) AS k
ORDER BY k

这篇关于如何使用 BigQuery 提取 JSON 对象中的所有键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！