BigQuery：是否有一个通用函数可以提取并转换json中的标量值

k3bvogb1 于 12个月前发布在其他

关注(0)|答案(2)|浏览(153)

我开始在google bigquery中使用json列。
我的目标是实现一个查询：根据路径提取json对象中的给定值，并返回其真实的SQL值。
示例：给定{"foo": {"bar": 123}}，我想得到$.foo.bar的整数值。但是，当查询包含json列的表时，我得到了一个字符串。
我知道这种行为是有据可查的
我正在寻找一个片段/配方/一种方法来实现一些东西，可以为我做标量转换。
我得到了一些不是有效的SQL，但代表我搜索的东西：

WITH
  base AS (
  SELECT
    JSON_QUERY( JSON '{"foo": {"unknown":123}}', '$.foo') AS col)
SELECT
  CASE JSON_TYPE(JSON_QUERY(col, '$.unknown'))
    WHEN 'null' THEN NULL
    WHEN 'number' THEN LAX_FLOAT64(JSON_QUERY(col, '$.unknown'))
    WHEN 'boolean' THEN LAX_BOOL(JSON_QUERY(col, '$.unknown'))
    WHEN 'string' THEN LAX_STRING(JSON_QUERY(col, '$.unknown'))
  ELSE
  -- array or map are not scalar - keep them as they are:
  JSON_QUERY(col, '$.unknown')
END as output
FROM
  base

字符串
任何帮助是高度赞赏！谢谢：）

JSON

来源：https://stackoverflow.com/questions/77715183/bigquery-is-there-a-generic-function-which-extracts-converts-a-scalar-value-i

2条答案

按热度按时间

yduiuuwa1#

WITH base AS (
  SELECT '{"foo": {"unknown":123}}' AS col
)

SELECT
  CASE 
    WHEN JSON_TYPE(JSON_QUERY(col, '$.unknown')) = 'null' THEN NULL
    WHEN JSON_TYPE(JSON_QUERY(col, '$.unknown')) = 'number' THEN CAST(JSON_EXTRACT(col, '$.unknown') AS FLOAT64)
    WHEN JSON_TYPE(JSON_QUERY(col, '$.unknown')) = 'boolean' THEN CAST(JSON_EXTRACT(col, '$.unknown') AS BOOL)
    WHEN JSON_TYPE(JSON_QUERY(col, '$.unknown')) = 'string' THEN CAST(JSON_EXTRACT(col, '$.unknown') AS STRING)
    ELSE JSON_QUERY(col, '$.unknown')
  END AS output
FROM base;

字符串
此查询使用JSON_EXTRACT函数提取值，然后使用CASE语句检查JSON类型并执行适当的转换。
确保将基本CTE中的示例JSON字符串替换为实际的列引用。

赞(0）回复(0）举报 12个月前

gc0ot86w2#

查询：

在查询output列expecting中只有一种数据类型，因此您可以考虑以下查询，而不是创建一个列：

select  *, if(type="string",LAX_STRING(col),null) stringcolumn, if(type="number",LAX_FLOAT64(col),null) intcolumn, if(type="boolean",LAX_BOOL(col),null) boolcolumn, from (select *,json_type(col)as type from base);

字符串

结果：

的数据
从上面的结果中，你可以选择不包含null值的列。例如，如果你的输入值是number，那么列stringcolumn和boolcolumn的值都是null。使用子查询而不是选择所有列，你可以选择intcolumn列。
根据我的理解，没有内置的BigQuery函数可以根据路径在json对象中提取一个精确的给定值并返回其真实的SQL值。所以如果你想在BigQuery中使用这个功能，你可以在Issue Tracker中提出一个功能请求。
有关更多信息，您可以参考这些link1，link2和link3。

赞(0）回复(0）举报 12个月前

我来回答

BigQuery：是否有一个通用函数可以提取并转换json中的标量值

2条答案

相关问题

热门标签

最新问答