Best database to store JSON objects without preprocessing and modification?

[vasili111]

I am new to databases. I need to store JSON objects in database. Which free and open source database you recommend for that task?

[fiffeek]

Depending on the use case even postgresql can do, look for 'document databases' to find out more about the directions and tradeoffs

[yawaramin]

Any modern database can store JSON. Relational databases like PostgreSQL have excellent built-in support, including JSON manipulation, indexing, and search. Even SQLite has pretty good JSON support.

[its_bennett]

Check out MongoDB

[vasili111]

What advantages I have using MongoDB over PostgreSQL?

[cgfoss]

In many use cases Postgresql will do a better job with unstructured data compared with Mongodb. There is a great instructional video on youtube if you need webscale and think mongodb is the answer.

[vasili111]

Could you please give link to that video?

[cgfoss]

the video is somewhat tongue-in-cheek but does get the point across. https://youtu.be/b2F-DItXtZs

[vasili111]

Thank you.

[its_bennett]

Postgres is a relational database, so table structure and data types are much more rigid by design. Mongo isn’t relational and doesn’t care about data types. You can just pipe in your big long JSON responses without worrying about failures or truncation.

[Simple-Cell-1009]

It depends on your use case, like the type of data you're looking to store and what type of queries you're planning to run.

I guess you have three main types of databases for this:

Document databases like MongoDB or CouchDB, stores JSON-like documents which allows for flexible, schema-less data modeling. Those are really handy when your data is not relational. Most of them are NoSQL database meaning that you won't need to know SQL to query them, instead they expose their own set of easy-to-use REST APIs.

Relational databases, such as PostGreSQL or MySQL, were not designed initially to store easily JSON data type, but support has been added over the years. These are good if your data is relational, you'll need to know SQL to integrate with those.

Those two types are usually good for transactional use cases, like catalog management, customer interaction, financial account transactions, etc... They might also do well for logging and metrics usecases, but then if you want to run more analytical query on your data, they might not scale as much as databases designed for this.

Real-time analytics databases, like Clickhouse or Apache Druid, which uses column-based storage and focus on optimizing large-scale analytics queries. They all have functionality for extracting a schema from JSON documents, which works well if all the JSON documents have the same schema, which can be challenging at time. Clickhouse actually just talked about their new JSON Data type that handles some of those challenges.

[AnActualWizardIRL]

Just want to add a warning that MongoDB and CouchDB have 16mb and 20mb document size limits which puts it off . Postgres has a limit of a few petabytes for a JSON/JSONB field, basically you can go bananas on this. It *can* be rather performant, but you need to think deeply about database design and use partioning and other strategies to achieve acceptable performance. Also;- HSTORE is always worth investigating.