I have a JSON in below format returning from procedure in snowflake
[
{
"A": "[{\"A\":\"5854\"}]"
},
{
"B": "[{\"B\":\"5681\"}]"
},
{
"FYM": "[{\"200701\":\"202112\",\""\":\"177\"}]"
}
]
I am expecting output in below format
| COL_NAME | VALUE | MIN_DT | MAX_DT |
|---|---|---|---|
| A | 5854 | ||
| B | 5681 | ||
| FYM | 177 | 200701 | 202112 |
Below is the code I have tried
SELECT DISTINCT object_keys(value)[0] AS col_name
FROM table(flatten(:tmp_array))
Above query returns below output
| COL_NAME |
|---|
| "A" |
| "B" |
| "FYM" |
Now to populate value column I used below query which returns null
SELECT DISTINCT object_keys(value)[0] AS COL_NAME, object_keys(value:value) AS VALUE
FROM table(flatten(:tmp_array))
Below is the second version to get VALUE column
select distinct regexp_replace(object_keys(value)[0],'"','') COL_NAME,TRIM(REGEXP_REPLACE(value, ':|\\{|\\}|"|\\[|\\]|\\\\|[a-z/-/A-z/./#/*]', ' ')) from table(flatten(:tmp_array)) )
Above query returns below output
COL_NAME |VALUE
----------|----------
A |5854
B |5681
FYM |200701 202112,177
Now the issues is FYM has 3 values still I haven't achieved expected. Not sure of the approach to create column MAX_DT,MIN_DT
Any help here please