snowflake extract all values from nested JSON enclosed with string

Viewed 45

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

0 Answers
Related