I have this issue. I am trying to ONLY get element number 2 (the second 'element') but I am not sure how to target that 'element'. the code below gives all element
select column,
a.value:element: ready
from table_name
,lateral flatten(input => RAW:root)a
context:
"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"
},
CodePudding user response:
Using this CTE just for data:
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
)
To get just the "second" element, hard code it!
SELECT
raw:root[1]:element:ready as ready,
raw:root[1]:element:Name as name
FROM table_name;
READY | NAME |
---|---|
"some valuable stuff here" | "some valuable stuff here" |
OR you can filter the FLATTEN results and only accept the 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
READY | NAME |
---|---|
"some valuable stuff here" | "some valuable stuff here" |
still quite gross, so maybe you want the "second element" thus some pre-filtered by IS_NULL_VALUE then a 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
gives:
READY | NAME1 |
---|---|
"some valuable stuff here" | "some valuable stuff here" |
BUT these are all order specific, and JSON is not normally considered order respecting, so it all seems rather dangerous.