Simple way to turn Multi Level JSON into Referenced DB Tables

[nxfxcom]

Hello,

I am loading large amounts of different JSON data sets (1000s of files in different formats). These are nested with references etc. Is there a simple way or tool to semi-automatically turn them into tables? For example:

JSON

{
  "ItemId" : "6004",
  "ItemName" : "Super Shoe",
  "categories" : [ {
    "displayName" : "Shoes",
    "level" : 0,
    "Id" : "12",
    "recordCount" : "61",
    "targetUrl" : "/shoes"
  }, {
    "displayName" : "Minis",
    "level" : 0,
    "Id" : "22",
    "recordCount" : "3",
    "targetUrl" : "/minis"
  }],

should create 2 tables… one is
ITEMS (Main table) with Name and Id
CATEGORIES with name, level, id, record count, and url

and then either in table 1 a reference column or a third table that is associations

Thank you

[Matt_D]

Hi @nxfxcom given the code snippet you shared it’s very easy to create, 2 or 3 tables, item table and category table or item table, category table and relationship table BUT you quote “1000s of files in different formats” if the JSON structure is different in each file then that would cause an issue.

If you have limited number of variations of JSON in 1000s of files which all lead to the 2 or 3 tables that should be possible with a loop as long as the files contain something that could be used to identify which way to parse the data.

Could you add some more context?