How to make a flat table from a rather complex json

larand54 · 2024-06-19 14:42:58

The json file I got is a very big one with lot of levels and would normally be split up into several tables but I have been asked to make one flat table of it.
To me it looks as very complicated task to solve so I would be happy to get some help to put me in the right direction.
I suppose that there are some very useful code in mormot. I've been looking a round a bit but it isn't that easy.

ab · 2024-06-19 14:57:23

Size in MB?
Estimate number of nested levels?
Has it a fixed fields layout, or something changing depending on the context?

larand54 · 2024-06-19 15:27:41

It has a fixed fields layout. I think it is created from a XML-file.
Just below 1MB > 24000 lines.
9 levels.

Last edited by larand54 (2024-06-19 15:46:35)

ab · 2024-06-19 15:48:04

I would try to define the structure as records and dynamic arrays, and let mORMot load it directly into memory.
Then write it into the database.

If the main format is a JSON array of JSON objects (which are complex), each JSON object needing to be written in the database, then I would read and process one object at a time.

larand54 · 2024-06-19 16:03:24

I'm not sure I understand the last sentence. I can show you the last part of the jsonfile if that helps.

            "DetaljReferens": [
              {
                "Referens": null, 
                "Referens_ReferensLista": "BSAB96:PR", 
                "Referens_ReferensKod": ""
              },
              {
                "Referens": null, 
                "Referens_ReferensLista": "SBEF:Byggdel", 
                "Referens_ReferensKod": "90"
              },
              {
                "Referens": null, 
                "Referens_ReferensLista": "PRODKOD", 
                "Referens_ReferensKod": ""
              }
            ]
          }
        }
      ]
    }
  }
}

ab · 2024-06-19 16:15:25

So it is not an array of objects, but an object with nested content.
I don't know how to make it "flat", since it is not an array as root.

1MB is not big, so I would go into filling some records and array of records with the value, then use it as high-level data structures to fill your DB with some meaningful content.

larand54 · 2024-06-19 16:49:55

I'll go for that, thanks ab.

mORMot Open Source

#1 2024-06-19 14:42:58

How to make a flat table from a rather complex json

#2 2024-06-19 14:57:23

Re: How to make a flat table from a rather complex json

#3 2024-06-19 15:27:41

Re: How to make a flat table from a rather complex json

#4 2024-06-19 15:48:04

Re: How to make a flat table from a rather complex json

#5 2024-06-19 16:03:24

Re: How to make a flat table from a rather complex json

#6 2024-06-19 16:15:25

Re: How to make a flat table from a rather complex json

#7 2024-06-19 16:49:55

Re: How to make a flat table from a rather complex json

Board footer