英文:
Use regex_replace to extract all value in json array and remove value after separate
问题
提取Snowflake中JSON数组中所有ID的方法是将正则表达式调整为提取冒号（:）之前的值。以下是您的查询的修改版本：
SELECT
player_army_load,
TRIM(REGEXP_REPLACE(player_army_load, ':.*$', ''), '{|"') AS element_id
FROM x
这将提取冒号（:）之前的所有值，如您所需的198002、198006和320906。
英文:
I want to be able to extract all ids from json array in snowflake before ':'
I have this json format for example: { "198002": 2115960, "198006": 604560, "320906": 302280 }
I have tried something like this:
select
player_army_load
,trim(regexp_replace(player_army_load,'\:.*$',''),'{|"') as element_id
from x
but this extract only first value, but I want 198002,198006,320906
extract all value before ":"
答案1
得分: 2
我看到您需要从JSON中提取键值，您可以使用OBJECT_KEYS函数：
select OBJECT_KEYS(PARSE_JSON(col1))
from jsontable;
结果：
[ "198002", "198006", "320906" ]
如果您想将结果作为字符串获取，可以使用ARRAY_TO_STRING函数：
SELECT ARRAY_TO_STRING( OBJECT_KEYS(PARSE_JSON(col1)), ',' )
FROM jsontable;
结果：
198002,198006,320906
英文:
I see you need to extract keys from the json, in this case you can use OBJECT_KEYS:
select OBJECT_KEYS(PARSE_JSON(col1))
from jsontable;
Result :
[ "198002", "198006", "320906" ]
And if you want to get your result as a string then use ARRAY_TO_STRING :
SELECT ARRAY_TO_STRING( OBJECT_KEYS(PARSE_JSON(col1)), ',' )
FROM jsontable;
Result :
198002,198006,320906
答案2
得分: 0
这是给你尝试在你的对象中使用for循环的代码部分：
const data = {1:"one", 2:"two", 3:"three", 4:"four"}
for(const i in data){
// 使用 i 执行你的任务
}
英文:
this is for you try for loop in your object
const data = {1:"one",2:"two", 3:"three", 4:"four"}
for(const i in data){
**perform your task with i**
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论