JSON files ingestion in SNOWFLAKE with diferent column structure

60 Views Asked by At

I have a Variant column in a table where I'm storing a JSON that looks like this:

{
  "Cash": null,
  "Id": "511",
  "Code": "Test"
}

I must uppercase only the columns without affecting the values and the structure, what I expect is this:

{
  "CASH": null,
  "ID": "511",
  "CODE": "Test"
}

It is very important to not remove the spaces as I'm using the following function to store the JSON into columns:

json_extract_path_text(column, '"CASH"' ) as Cash

If you wonder why I need this task is because I'm receiving many files where the columns sometimes are uppercase and sometimes are lowercase and this is a problem because the function above is case sensitive.

I already tried this:

replace(regexp_replace(data_csv,'"([^"]+)":',upper($1)),'{{','{') as data_csv

but it's converting EVERYTHING in UPPERCASE.

0

There are 0 best solutions below