mysql - 在 BigQuery 中查询表

Question

背景

我有一个包含 1 列“数据”的表，其中包含 BigQuery 中的“JSON”，如下所示。

 data    
 {"name":"x","mobile":999,"location":"abc"}
 {"name":"x1","mobile":9991,"location":"abc1"}

现在，我想使用 groupby 函数:

SELECT
    data
FROM
    table 
GROUP BY 
    json_extract(data,'$.location')

这个查询抛出一个错误

expression JSON_EXTRACT([data], '$.location') in GROUP BY is invalid

所以，我将查询修改为

SELECT
    data, json_extract(data,'$.location') as l
FROM
    table 
GROUP BY
    l

这个查询抛出错误

Expression 'data' is not present in the GROUP BY list

查询

我们如何在 group by 子句中使用 JSON 字段？

用 JSON 填充列的限制是什么(在查询上下文中)。

Answer 1

您正在按位置对某些内容进行分组，但您没有为 data 字段使用聚合函数，因此编译器不知道选择哪个或您在源上聚合什么。

只是为了说明这个例子，我编译了这个使用 group_concat 工作的测试查询:

select group_concat(data),location from
(
select * from
(SELECT '{"name":"x","mobile":999,"location":"abc"}' as data,json_extract('{"name":"x","mobile":999,"location":"abc"}','$.location') as location),
(SELECT '{"name":"x","mobile":111,"location":"abc"}' as data,json_extract('{"name":"x","mobile":111,"location":"abc"}','$.location') as location),
(SELECT '{"name":"x1","mobile":9991,"location":"abc1"}' as data,json_extract('{"name":"x1","mobile":9991,"location":"abc1"}','$.location') as location)

) d
group by location

并返回:

+-----+---------------------------------------------------------------------------------------------------+----------+--+
| Row | f0_                                                                                               | location |  |
+-----+---------------------------------------------------------------------------------------------------+----------+--+
| 1   | {"name":"x","mobile":999,"location":"abc"},"{""name"":""x"",""mobile"":111,""location"":""abc""}" | abc      |  |
+-----+---------------------------------------------------------------------------------------------------+----------+--+
| 2   | {"name":"x1","mobile":9991,"location":"abc1"}                                                     | abc1     |  |
+-----+---------------------------------------------------------------------------------------------------+----------+--+

BigQuery's Aggregate Functions documented here