using flatten in snowflake: How to target specific nested child in JSON?

Question

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"
            },

Answer 1

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.