我有這個問題。我試圖只獲取元素編號 2（第二個“元素”），但我不確定如何定位該“元素”。下面的代碼給出了所有元素
select column,
a.value:element: ready
from table_name
,lateral flatten(input => RAW:root)a
語境：
"root": [
{
"element": {
"ready": "some stuff here",
"max": "some stuff here"
},
"bee": {
"value": "some stuff here",
"name": "some stuff here"
}
},
{
"element": {
"ready": "some valuable stuff here",
"Name": "some valuable stuff here"
},
uj5u.com熱心網友回復：
僅將此 CTE 用于資料：
WITH table_name as (
SELECT PARSE_JSON('{"root": [
{
"element": {
"ready": "some stuff here",
"max": "some stuff here"
},
"bee": {
"value": "some stuff here",
"name": "some stuff here"
}
},
{
"element": {
"ready": "some valuable stuff here",
"Name": "some valuable stuff here"
}
}]}') as RAW
)
要獲得“第二個”元素，請對其進行硬編碼！
SELECT
raw:root[1]:element:ready as ready,
raw:root[1]:element:Name as name
FROM table_name;
| 準備好 | 姓名 |
|---|---|
| “這里有一些有價值的東西” | “這里有一些有價值的東西” |
或者您可以過濾 FLATTEN 結果并且只接受 index = 2;
select
a.value:element:ready as ready
,a.value:element:Name as name
from table_name
,lateral flatten(input => RAW:root) a
WHERE a.index = 1
| 準備好 | 姓名 |
|---|---|
| “這里有一些有價值的東西” | “這里有一些有價值的東西” |
仍然很惡心，所以也許你想要“第二個元素”，因此一些被 IS_NULL_VALUE 預過濾然后是 QUALIFY
WITH table_name as (
SELECT PARSE_JSON('{"root": [
{"non-element":123},
{
"element": {
"ready": "some stuff here",
"max": "some stuff here"
},
"bee": {
"value": "some stuff here",
"name": "some stuff here"
}
},
{
"element": {
"ready": "some valuable stuff here",
"Name": "some valuable stuff here"
}
}]}') as RAW
)
select
a.value:element:ready as ready
,a.value:element:Name as name1
from table_name
,lateral flatten(input => RAW:root) a
WHERE is_null_value(a.value:element) = false
QUALIFY ROW_NUMBER() OVER (ORDER BY a.index ) = 2
給出：
| 準備好 | NAME1 |
|---|---|
| “這里有一些有價值的東西” | “這里有一些有價值的東西” |
但是這些都是特定于順序的，并且 JSON 通常不被認為是尊重順序的，所以這一切似乎都相當危險。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/452090.html
上一篇：使用顛簸變換截斷字串的一部分
下一篇：如何訪問不同的JSON密鑰？