I have a database with a column having JSON data that doesn’t have a clear structure. It contains multiple nested tables. This data come from different surveys. I need to use SQL dynamic to automate the process of dividing this data into multiple tables with relational data that can be analysed later with T-SQL.
Do you have any idea how can JSONs that don’t have a structure be transformed into relational data?
This is the logic:
Source
| ID | JSON |
|---|---|
| 1 | {"A":"1","B":{"X":"AAA","Y":"BBB","C":{"AC":"1","BC":"2"}} |
PrincipalTable
| ID | A | B |
|---|---|---|
| 1 | 1 | 1 |
Table1
| B | X | Y | C |
|---|---|---|---|
| 1 | AAA | BBB | 1 |
Table2
| C | AC | BC |
|---|---|---|
| 1 | 1 | 2 |
2
Answers
importJson("{absolute path to JSON file}", {schema: "{schema name}", table: "{table name}", tableColumn: "{column name}"})
few years late, but we created a library that automatically does that
https://github.com/dlt-hub/dlt