I'm working with some complex JSON data that represents groups of products and their details, with multiple layers of nesting—think of it as products, their variations, and associated metadata all structured in a hierarchy. I originally considered using SQL for storage but I'm unsure how to approach it since this data could get quite large and complicated. Ideally, I'd like to save it as JSON since it's easier to manipulate, but I need to be able to access individual data points and export them to CSV. Straightforward conversion to CSV hasn't worked well because of the overwhelming number of columns. What's the best way to store this kind of data while still being able to export it in a readable format?
5 Answers
Using PostgreSQL, I store my JSON data as a jsonb type. It works well because you can retain the entire structure while also having flexibility for queries. I recommend pairing the jsonb data with a couple of regular columns or even a child table for any fields that you’ll need to access regularly or for export.
Yep, jsonb is great for that! Just remember to keep it organized if you need efficient access.
One alternative is to create a column specifically for long strings, which can handle large datasets without needing the full complexity of JSON storage. This might help depending on your use case.
Before you decide, think about whether storing it as JSON is really the best route. Converting it to normalized SQL tables might work better for your needs. If your requirements allow it, explore NoSQL options like MongoDB which can handle JSON natively and efficiently.
I’ve been toying with the idea of flattening the data. Like having separate columns for groups and line items, and storing customization data in a more open format like comma-separated values. It's a lot simpler for SQL.
All major databases, including PostgreSQL, SQLite, and MySQL/MariaDB, support JSON formats. Make sure you are using the most suited one for your project needs. Storing JSON is useful but be cautious; it doesn’t always scale well, especially if your schema changes frequently.
Exactly! I thought about spreading it across multiple tables and using foreign keys, but managing those relationships can complicate things.
Just curious, why did you opt to store your data in JSON initially? Generally, each level of nesting could represent a separate table with foreign keys in a relational database. What are you aiming for?
The system is designed to send data via API from the frontend, which typically uses JSON. Since it's an ordering system, it holds all product-related info and options selected by users. It needs to be displayed efficiently to our team for data checks.
That's interesting! I like the idea of storing it as binary but still being able to query like normal. I'm on MySQL, but I think I can find a similar method.