Oh no, apologies for the lack of clarity. I would not get the whole thing and build it in the application layer before sending it to the client.

If I want a particular array item nested a few levels in a JSON, the query as JSON object instead of query as an SQL is just a bit easier to put together (which is what i mean by pure JS). Sometimes, putting complex queries together takes fewer lines of code. Neither is really better or worse.

But I want the database doing the work and want to transform as little as possible on the application layer. Sometimes it is inevitable, but sub-optimal, for sure.

In terms of anything where you need to do a lot of joins when querying -- NoSQL is not good and I would not advise Mongo at all. It depends on what you are building. A lot of applications (and even games) now just pass JSONs around. If I show a user profile, I get that json. If, on the profile, I need to show where she lies in a leaderboard, that react component hits a separate API call to get the data needed for it, etc.

But no difference really. And yes, if the JSON is just a column in postgres, even the early stuff is nearly as easy as it is in postgresql.

If I were looking at a DB for a simple key/value store, I would not look at Mongo. Others here likely know better, but I pick up Redis (out of habit, but also it is awesome even if overkill in many scenarios). Mongo would do it but I would not do so except temporarily. I tend to throw Redis in my stacks early in development as I tend to need it later.

BTW by no means do I love Mongo. For example, Mongo is awful when it comes to things simiilar to joining in SQL land. You end up in these hellscapes where there is no choice but to store a ton of redundant data. Is it the end of the world? No, storage is cheap. But is it good? No.

And I am old so I do not find the value in arguing about tools. It is like me hating a hammer. I am interested in what is better and what folks here think about things coming down the pipeline, or whether Arango is worth looking at, etc. But I do appreciate your sort of inquiry as I have learned from it -- for example I did not know you can index inner fields of JSONs. I really need to revisit is as recent versions of PG seem to get better and better.

Context matters a ton. If you are going right to production with something, that is not really where I operate. But in general, as many have said, if more than one thing will do it, the best tool is the one you know best. You are likely to implement it efficiently, etc.

Thanks. I can see the utility of having separate components do their "own" full lifecycle even if I usually do tend to approach it in other ways, it's a valid take and can make sense (although as you said I think it can be done with SQL).

I agree with you on redis-like utilities being better for pure KV stores. If you resort to that sort of thing regularly and want to give another look into postgres you might want to give Elixir a try. It has first class support for postgres with Ecto, and you can use redis-like stores from the language itself, skipping serialization (a note is that, one of the valuable things in redis usually comes from being in a separate instance, many times managed automatically, and so almost being a permanent cache - to have exactly the same properties you would need to dig a bit deeper in Elixir, but still pretty doable with the language building blocks).