PostgreSQL JSON field type vs MongoDB

Question

I am building some app where part of it will have pretty much unstructured and changing data which I would like to dump as JSON. As a primary DB I am using PostgreSQL. I have never used JSON field type, but have used MongoDB before. I don't need anything fancy, 99% of the time it will be just storing and getting the data and querying by some fields of the JSON and it is of a scale that one instance of PostgreSQL could handle, nothing epic.

My question is, is PostgreSQL JSON field comparable to using MongoDB? Are there any limits or stumbling blocks that I should now about before choosing PosgresSQL for this? Bonus question : Is there an easy way to view and edit that JSON field by hand like I can when using Studio 3T app for MongoDB?(I know PostgreSQL DB apps are not the greatest for some reason..)

Answer 1

You'll not have any problem at all using Postgres for your json data. It takes some time to get used to the json and array functions in Postgres, but they are great and will cover most of your needs. Json support in Postgres is mature and awesome.

Try to think hard before storing data in json fields, though. It's an easy way, but it can come back and bite you if your data is in fact relational. The good old normal forms does hold true even today.

I have used MongoDB in a couple of project and I doubt I'll ever use it again. I think that most real world use cases are in fact relational. Postgres has struck a perfect balance (in my view) between relational and documents with the great support for json.

If you just want to store a "blob" of json data you can use the json datatype. But if you know that you will need to manipulate the objects or pluck values from them, you should use jsonb. Jsonb will check the validity of your json data as well. I also think that jsonb is more efficient to index.

Using the json datatype or array of json as input parameter in Postgres functions is fantastic imo. I often get json from my API server that needs to be broken up into smaller peaces when data needs to be store as relational. This is easily done in Postgres. I also use this the other way around sometimes, i.e. a Postgres function consolidates relational data and packages it nicely into REST friendly json objects for output. This way I can hide the fact that I actually store the data in a normalised database.