ambiguous column name 'VALUE'

Question

Any idea to overcome ambiguous column with snowflake lateral flatten function error with below logic is much appreciated. I'm trying to flatten the nested JSON data using the below query by selecting the value from variant column, However getting ambiguous column name 'VALUE' error with lateral flatten function. Can someone help me to achieve the desired output. Issue here is the JSON key name is coming as "value" and I couldn't get that data using lateral flatten. Desired output has been attached as image to this thread.

Sample JSON Data

 {"issues": [
{
"expand": "a,b,c,d",
"fields": {
"customfield_10000": null,
"customfield_10001": null,
"customfield_10002": [
    {
    "id": "1234",
    "self": "xxx",
    "value": "Test"
    }
],
},
"id": "123456",
"key": "K-123"
}
]}*


*select
    a.value:id::number as ISSUE_ID,
    a.value:key::varchar as ISSUE_KEY,
    b.value:id::varchar as ROOT_CAUSE_ID,
    **b.value:value::varchar as ROOT_CAUSE_VALUE**
from
    abc.table_variant,
    lateral flatten( input => payload_json:issues) as a,
    lateral flatten( input => a.value:fields.customfield_10002) as b;*

Answer 1

Try

b.value:"value"::varchar

Answer 2

WITH CTE AS 
(select parse_json('{"issues": [
{
"expand": "a,b,c,d",
"fields": {
"customfield_10000": null,
"customfield_10001": null,
"customfield_10002": [
    {
    "id": "1234",
    "self": "xxx",
    "value": "Test"
    }
],
},
"id": "123456",
"key": "K-123"
}
]}')
 as col)
 
select 
a.value:id::number as ID, 
a.value:key::varchar as KEY, 
b.value:id::INT as customfield_10002,
b.value:value::varchar as customfield_10002_value

from cte,
lateral flatten(input => cte.col, path => 'issues') a,
lateral flatten(input => a.value:fields.customfield_10002) b;