Forum Discussion
Error ingesting json files in warehouse table
A final update for now at least: Microsoft has confirmed this is a bug, they hope to fix in the 1st quarter of 2026.
In the mean time they suggest to ingest it as a string and than use a notebook to parse the json array back into its original columns, or change the source, which for us wasn't an option.
Since it took ages for them to got to this stage I had already figured out I could use a notebook to read the json files in the lakehouse, read them into a dataframe, and when all files were read, write that dataframe to a table, in my case with an option "overwrite" since this is just a raw staging table.Final kicker: They want me to give permission to archive the case (no doubt because of their KPI's), but the bug is not registered on MS's known issue list. I want them to document it there, but they for some reason don't want to do that, they want to keep it internal, so now my Dutch stubberness is playing up .
The python code was quite simple in the end, even though we had multiple nested arrays. I used co-pilot to do the heavy lifting for me since i had to alias about 30 columns since i had to put the data in an existing table and the names needed to match
These are the libraries I needed:
Folder path to read json files
Exploding the main body of the json (just part of it, as example)
Thats it in a nutshell
Hi All,
A small update, The data engineering team is now trying to schedule a teams meeting with me, so it is still in the hands of MicroSoft.
I think, in de mean time, I have a working notebook, but the bussines analyst still needs to verify the data.
If at all possible, I want the old solution to start working again, the notebook way seems very convoluted and the fact Fabric was great in dealing with jsons in the past, using a notebook feels as a step back (Granted me not being very versed in SparkSQL doesn't help either, I had a lot of help from AI to get it to work, and it always bugs me if I dont fully understand what I have written...SparkSQL turns out to be quite different from SQL, once you get into more complex fucntions)
Some snippets of the code below, especially having to define each datatype for each key is a lot of work which is prone to error, and as i said we have several nested arrays getting returned from the API. I have removed quite a few lines of code from the sample below. Maybe the python route is easier, I don't know?
==============
Hi smeetsh,
Thanks a lot for the update. It’s good to hear you have a notebook running, even though it feels a bit complicated right now. I understand what you mean about SparkSQL being tricky compared to the way Fabric used to handle JSON, it can feel like extra work having to define all those data types.
It’s great that your data engineering team is already in touch with Microsoft. Hopefully, that will help bring back the old solution, since that was much simpler for your use case. In the meantime, keeping the notebook as a backup makes sense.
If you want to explore alternatives, using Python in a notebook might make working with nested JSON a bit easier than SparkSQL, since you don’t always need to write out the full schema by hand. But ideally, once the engineering team and Microsoft confirm things, you may not need this workaround long term.
Thank you for using the Microsoft Fabric Community Forum.Hi smeetsh,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
Good morning, No it has not been resolved and to be honest my last session with MS was downright painfull. The person from the MS data engineering team had a lot of troubles understanding how an API call wrote to a json file in the lakehouse (which is about as standard as it gets)
MS went back to do more homework.
