1

Using .net 4.8 I need to import a large (7GB+) unstructured Json file into SQL Server.

Yes, 7GB is correct and yes, I know this is not ideal, but this is the problem that landed on my plate. Please don't comment on why I'm trying to take bad unstructured data into a structured database or why I'm doing this. I have a plan to make this clean, good, structured data. For now, I just need to solve this problem. This problem is a one-time occurance - thanks.

By saying "unstructured", it looks like the screenshot below where the top level elements are GUIDs or user names (all unique) and the data structures under those are inconsistent. Therefore I can not create a type to de-serialize to custom class like:

o = serializer.Deserialize<MyObject>(reader);

enter image description here

I can, however, get a list of all the top level elements like this (by just copy/paste from the firebase web page to a c# console app) and loop through items, use each item's text to do a fetch or query into the large json file to get their json:

enter image description here

Then perhaps from c# I could query the blob of json and pull all data for each element one at a time keeping in mind that I won't be able use a class like "MyObject" to deserialize like this:

o = serializer.Deserialize<MyObject>(reader);

My goal is to get the json for each top element, then loop through its' child elements: issues projects images reports etc.

Then for each of those, do the same, loop through their child elements, etc. For every element, as long as I know what type of element it is and what it's parent element is, then I will know how to insert it into sql server.

Another problem I have is reading the json file into a string variable I get a system out of memory exception

using (StreamReader streamReader = new StreamReader(path, Encoding.UTF8))
{
    readContents = streamReader.ReadToEnd();
}

Thank you for any help you can offer.

spacedog
  • 446
  • 3
  • 13
  • 2
    For a JSON that large, I'd be inclined to use something like [Newtonsoft's JsonTextReader](https://www.newtonsoft.com/json/help/html/ReadJsonWithJsonTextReader.htm) – David784 Jul 06 '21 at 21:35
  • 4
    A 7 gig json file is extreme and is likely the wrong solution, trying to deserialize it all at once is asking for trouble. It sounds like you should rethink the problem if you can... if you cant, you will need to parse the file iteratively in a stream and just take what you need. – TheGeneral Jul 06 '21 at 21:41
  • I pressume you can convert it into a Jobject https://www.newtonsoft.com/json/help/html/ParseJsonObject.htm, and then just iterate the values and check "whcih" tipe of your dfferent types could be. but as mention before, a 7gb file does not sound correct – TiGreX Jul 06 '21 at 22:14
  • Without any specific details about what you are trying to do -- or even better a [mcve] -- we can only recommend you stream through the file using `JsonTextReader` as shown in [How to parse huge JSON file as stream in Json.NET?](https://stackoverflow.com/q/43747477/3744182). – dbc Jul 07 '21 at 18:13
  • *Another problem I have is reading the json file into a string variable I get a system out of memory exception* -- then don't do that. Deserialize directly from a stream as shown in [Json.Net deserialize out of memory issue](https://stackoverflow.com/q/33480477/3744182). – dbc Jul 07 '21 at 21:44

0 Answers0