在雪花表中有這樣的值：
{“值”：“0”，“_my_id1”：“111”，“_my_id2”：“123”，“some_tag”：“隨機”，“something_else”：“random1”，“seg”：“123,234,234,444,555”“some_id ": "000987", "plus_tag": "是" }
從此標簽中，需要檢索其中包含“ID”的所有鍵，尋找如下結果：
“_my_id1”：“111”，“_my_id2”：“123”，“some_id”：“000987”
結果可以是 json 或 string
uj5u.com熱心網友回復：
使用以下作為資料 -
with data_cte as
(
select column1 from values('{
"value": "0",
"_my_id1": "111",
"_my_id2": "123",
"some_tag": "random",
"something_else": "random1",
"seg": "123,234,234,444,555",
"some_id": "000987",
"plus_tag": "yes"
}')
)
select * from data_cte;
-------------------------------
| COLUMN1 |
|-------------------------------|
| { |
| "value": "0", |
| "_my_id1": "111", |
| "_my_id2": "123", |
| "some_tag": "random", |
| "something_else": "random1", |
| "seg": "123,234,234,444,555", |
| "some_id": "000987", |
| "plus_tag": "yes" |
| } |
-------------------------------
使用 ID 鍵查詢以獲取所有資訊 -
with data_cte as
(
select column1 from values('{
"value": "0",
"_my_id1": "111",
"_my_id2": "123",
"some_tag": "random",
"something_else": "random1",
"seg": "123,234,234,444,555",
"some_id": "000987",
"plus_tag": "yes"
}')
)
select key,value from data_cte,
lateral flatten(input=>parse_json(column1))
where key like '%id%';
--------- ----------
| KEY | VALUE |
|--------- ----------|
| _my_id1 | "111" |
| _my_id2 | "123" |
| some_id | "000987" |
--------- ----------
從上面的輸出構造回 JSON 為 -
with data_cte as
(
select column1 from values('{
"value": "0",
"_my_id1": "111",
"_my_id2": "123",
"some_tag": "random",
"something_else": "random1",
"seg": "123,234,234,444,555",
"some_id": "000987",
"plus_tag": "yes"
}')
)
select to_json(object_construct(key,value)) as col from data_cte,
lateral flatten(input=>parse_json(column1))
where key like '%id%';
----------------------
| COL |
|----------------------|
| {"_my_id1":"111"} |
| {"_my_id2":"123"} |
| {"some_id":"000987"} |
----------------------
輸出為字串 -
with data_cte as
(
select column1 from values('{
"value": "0",
"_my_id1": "111",
"_my_id2": "123",
"some_tag": "random",
"something_else": "random1",
"seg": "123,234,234,444,555",
"some_id": "000987",
"plus_tag": "yes"
}')
)
select '{'||listagg(key||':'||value,',')||'}' as col from data_cte,
lateral flatten(input=>parse_json(column1))
where key like '%id%';
------------------------------------------
| COL |
|------------------------------------------|
| {_my_id1:111,_my_id2:123,some_id:000987} |
------------------------------------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/463397.html
上一篇：在jqbash中獲取空值
下一篇：如何將字串轉換為字典串列？