英文:
Unpack a specific key from json column
问题
我的数据如下所示：
col1
[{"key":"1", "count":5, "desc":"blah"},{"key":"2", "count":3, "desc":"meh"}]
[{"key":"2", "count":23, "desc":"qwe"},{"key":"44", "count":0, "desc":""}]
我想创建一个新的列，其中只包含"key"的值：
new_col
["1", "2"]
["2", "44"]
我尝试以各种方式解析JSON，但没有找到一个合理的方法来完成它。
英文:
My data look like:
col1
[{"key":"1", "count":5, "desc":"blah"},{"key":"2", "count":3, "desc":"meh"}]
[{"key":"2", "count":23, "desc":"qwe"},{"key":"44", "count":0, "desc":""}]
I want to create new column which will include only the "key"'s values:
new_col
["1", "2"]
["2", "44"]
I tried to parse the json in some ways but didn't find a reasonable way to get it done.
答案1
得分: 0
看起来你的一个单元格中有几个JSON对象。你可以在变体列上使用FLATTEN函数，将其展开为多行，然后提取其中的键，并将其聚合为一个数组。可以尝试以下代码：
SELECT ARRAY_AGG(FLATTENED.value:key) AS new_col
FROM my_variant_table, -- 替换为你的表名
LATERAL FLATTEN(input => col1) AS FLATTENED
GROUP BY col1;
-- 我使用GROUP BY来确保行的唯一性
英文:
It looks like you have a couple of JSON object in one cell. You can use FLATTEN on your variant column to be able to select from it as it was in multiple rows. Then you extract the key from it and aggregate as an array. Try this:
SELECT ARRAY_AGG(FLATTENED.value:key) AS new_col
FROM my_variant_table, -- replace with your table name
LATERAL FLATTEN(input => col1) AS FLATTENED
GROUP BY col1;
--group by I used to ensure the uniqueness of the rows
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论