Snowflake - Lateral flatten

{
  "area": {
    "id": "TEST1"
    "value": "TEST1"
  }, 
{  "testcom": {
     "conditions": [
     {
        "assignedto": {
          "workphone":NULL
        },
        "isClause":false
     }
    ],   
    "iscritical": true,
    "value": "Conditon"
 }
} 
} 

Hi, the output of json structure is as above

questions answer      isClause
testcom   conditions  FALSE

Need the output as above, i have used the below SQL, not able to get the output as expected, the source column is Variant, can someone please check...

select 
lvl_1.key,
lvl_1.value 
from 
    (
    select 
    parse_json(col1) as src 
    from 
    table1
    )xyz 
,lateral flatten(input => xyz.src) lvl_1,
,lateral flatten(input => lvl_1.value) lvl_2,
,lateral flatten(input => lvl_2.value:isClause) lvl_3

So as noted, your provided string is not JSON.

the first sub object area is missing a comma between id and value

this works though:

select PARSE_JSON('{ "area": {"id": "TEST1", "value": "TEST1"}}');

then after area there is a start of an object, but it is not named, this it valid inside a object, so lets name it missing_name_a

select 
    PARSE_JSON('{ 
        "area": {"id": "TEST1", "value": "TEST1"},
        "missing_name_a" : {  
            "testcom": {
                "conditions": [
                    { "assignedto": { "workphone":NULL }, "isClause":false }
                ],   
                "iscritical": true,
                "value": "Conditon"
            }
        } 
    } ') as json

this is valid JSON also.

now if we turn that into a CTE we can select from it, and show how to access the only array in the data:

with cte_json_data as (
select 
    PARSE_JSON('{ 
        "area": {"id": "TEST1", "value": "TEST1"},
        "missing_name_a" : {  
            "testcom": {
                "conditions": [
                    { "assignedto": { "workphone":NULL }, "isClause":false }
                ],   
                "iscritical": true,
                "value": "Conditon"
            }
        } 
    } ') as json
)
select j.*
    ,j.json:missing_name_a:testcom:conditions
from cte_json_data as j

now we can FLATTEN that array:

with cte_json_data as (
select 
    PARSE_JSON('{ 
        "area": {"id": "TEST1", "value": "TEST1"},
        "missing_name_a" : {  
            "testcom": {
                "conditions": [
                    { "assignedto": { "workphone":NULL }, "isClause":false }
                ],   
                "iscritical": true,
                "value": "Conditon"
            }
        } 
    } ') as json
)
select j.*
    ,a.value
from cte_json_data as j
    ,table(flatten(input=>j.json:missing_name_a:testcom:conditions)) as a

so to access isClause you:

with cte_json_data as (
select 
    PARSE_JSON('{ 
        "area": {"id": "TEST1", "value": "TEST1"},
        "missing_name_a" : {  
            "testcom": {
                "conditions": [
                    { "assignedto": { "workphone":NULL }, "isClause":false }
                ],   
                "iscritical": true,
                "value": "Conditon"
            }
        } 
    } ') as json
)
select j.*
    ,a.value:isClause as is_clause
from cte_json_data as j
    ,table(flatten(input=>j.json:missing_name_a:testcom:conditions)) as a

but those objects can be pushed into FLATTEN if you do not know the object names..

but it is gross:

with cte_json_data as (
select 
    PARSE_JSON('{ 
        "area": {"id": "TEST1", "value": "TEST1"},
        "missing_name_a" : {  
            "testcom": {
                "conditions": [
                    { "assignedto": { "workphone":NULL }, "isClause":false }
                ],   
                "iscritical": true,
                "value": "Conditon"
            }
        } 
    } ') as json
)
select
    m.key as questions
    ,a.key as answer
    ,aa.value:isClause as is_clause
from cte_json_data as j
    ,table(flatten(input=>j.json:missing_name_a)) as m
    ,table(flatten(input=>m.value)) as a
    ,table(flatten(input=>a.value)) as aa

gives: