我在BigQuery表中有一个列,看起来像这样。
{"name": "name1", "last_delivered": {"push_id": "push_id1", "time": "time1"}, "session_id": "session_id1", "source": "SDK", "properties": {"UserId": "u1"}}
有没有办法在GBQ中获得这样的输出?(基本上将整个列压平成不同的列)
name last_delivered.push_id last_delivered.time session_id source properties.UserId
name1 push_id1 time1 session_id1 SDK uid1
我希望这个过程是动态的。
假设对于下一行......可能还有2个新属性,如年龄,工作等,对于其他一些行,可能也会少一些。因此它应该自动拾取列名称。(如果值不存在,则可能填充NULL)
有没有办法在BigQuery中做到这一点。(我部分知道如何在python中完成它,因为我正在处理非常大量的数据,如果时间太长,在Python中做很多事情)
发布于 2019-03-20 14:20:30
您可以使用JSON_EXTRACT_SCALAR。
WITH json_table AS (
SELECT '{"name": "name1", "last_delivered": {"push_id": "push_id1", "time": "time1"}, "session_id": "session_id1", "source": "SDK", "properties": {"UserId": "u1"}}' AS json_field
)
SELECT
JSON_EXTRACT_SCALAR(json_field, "$.name") AS name,
JSON_EXTRACT_SCALAR(json_field, "$.last_delivered.push_id") AS last_delivered_push_id,
JSON_EXTRACT_SCALAR(json_field, "$.last_delivered.time") AS last_delivered_time
FROM json_table
https://stackoverflow.com/questions/-100006474
复制相似问题