This is my first post, please forgive me if it’s not the most beautiful post you’ll read. 😃
In my career as a developer, I often find myself spending a lot of time on the lengthy and resource-intensive task of extracting data from JSON files and placing them into relational databases so that data querying for analysts and others is facilitated by the use of SQL.
These are very time-consuming activities with a fairly high risk of making errors if not handled correctly.
For this reason, I have created a tool that automatically performs this task.🥳
What does this tool consist of?
In practice, JSONs are saved as JSONL within a text file.
Each individual line is analyzed to understand the structure of all the JSON paths within them.
Once this analysis is complete, a script creates the necessary structures for a PostgreSQL database (and soon for Oracle, Snowflake, etc.).
The result is an SQL script to be executed on the database, providing ready-to-use structures for the JSON data.
I have saved countless hours compared to before, and above all, if I receive any JSON input today, I no longer have to worry about its structure or writing the linking keys between the various tables.
You should know, in fact, that I have very complex JSONs, with up to 15 hierarchical levels, and... it works perfectly.
The interesting aspect of this utility is that it also automatically adapts to changes. Any new branches or variations are automatically detected, maintaining backward compatibility with already loaded data. You just need to rerun the script if changes are reported.
This utility also works if the input refers to XML!
Now the question I have for you is this: is this utility only useful to me, or are there other developers who might find it necessary? Let me know because I'm curious. 😃
Top comments (4)
Excellent job, this makes so much sense and it's so much easier to integrate into db.
Does it work for mysql ?
In a certain sense, yes, in two ways.
Starting from the PostgreSQL database, you can read the data as a source, or you can first export the data in CSV format and import the exported CSV files from the PostgreSQL database in MySQL.
It depends on how you prefer to do your workflow.
If you have some sample data you'd like to send me, I can create a custom demo for your use case.
How about JSONB? Do you consider a better option than JSON?