Flattening Json In Snowflake using regexp in Json Path?

Question

I met a problem in flattening the json into a relational table.

for example I have a json file like the below, How can I flatten the table content in both sheets:'sheet:1':'section 1':table and sheets:'sheet:2':'section 1':table ??

the number of sheet and section changes in each json file. is there any way to use regular expression in the json path? each json path of each file is following the same pattern, but the keys are not the same...

{
  "extraction date": {
    "month": "OCTOBER",
    "monthValue": 10,
    "year": 2020
  },
  "fileName": "test_1.xls",
  "number of sheets": 2,
  "sheets": {
    "sheet:1": {
      "content": {
        "conversion state": "Success",
        "section 1": {
          "meta": {
            "Remark": "This is the remark",
            "Row: 4": "this is the title"
          },
          "table": [
            {
              "col1": null,
              "col2": "2020-07-14"
              "Row": 9
            },
            {
              "col1": null,
              "col2": "2020-07-14"
              "Row": 10
            }
          ]
        }
      },
      "name": "Sheet1",
      "sections": 1
    },
    "sheet:2": {
      "content": {
        "conversion state": "Success",
        "section 1": {
          "meta": {
            "Remark": " null",
            "Row: 4": "title a"
          },
          "table": [
            {
              "col1": null,
              "col2": "2020-07-14",
              "Row": 8
            },
            {
              "col1": null,
              "col2": "2020-07-14",
              "Row": 9
            }
          ]
        }
      },
      "name": "mySheetName",
      "sections": 1
    }
  }
}

question from:https://stackoverflow.com/questions/65917220/flattening-json-in-snowflake-using-regexp-in-json-path

Answer 1

Waitting for answers