> A client should use appropriate joins to get the data from the database & then do the transformation
Please don't set up "object joins" against an object/key/value table. It's better to flatten the highly used bits of the json out into the table that hosts the "source json" as a blob in one column. There is a place in our codebase where someone did these "object joins", and it's a nightmare to work with. After about two months, there is a comment in one of the tests I found, "I really regret this pattern now". Now we have enough customer data in that object "kv table" that we can't migrate back to json + select flattened fields without extreme pain.
Many of these blog articles seem to be written by devs that have only really experienced the user-facing application side of things, and really don’t realise the sheer number of financial, analytic & business systems, that keep the world spinning round, that are all happily buzzing away using SQL.
Moaning about JSON manipulation in SQL is madness. A client should use appropriate joins to get the data from the database & then do the transformation. Storing JSON when you actually need to perform operations on said data makes 0 sense. It’s a huge overhead, an utter nightmare to index etc.