英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论