Question for people who actually write app and SQL code: besides convenience, what is the upside of working with JSON in SQL over having your app construct and parse JSON objects, but storing the data in a database using more primitive types? My relatively inexperienced brain is telling me that it’s probably over complex to store and manipulate JSON objects at the DB level.
I use Postgres JSON functions to return nested results. The database itself contains no JSON; just a well-normalised data model. However, the queries return nested JSON in the format required by the application, e.g. for rendering an HTML template or returning JSON to the client in a single round trip. Check out the old dogs can sort of learn new tricks in this great article: https://www.scattered-thoughts.net/writing/sql-needed-struct...
> JSON functions to return nested results. The database itself contains no JSON; just a well-normalised data model. However, the queries return nested JSON in the format required by the application
Entirely valid usecase, since the client application is likely going to parse some cartesian product of tabular relationship data into "normalized" JSON array of objects anyways.
Generally, generating the JSON response directly for consumption in the DB is faster.
> what is the upside of working with JSON in SQL over having your app construct and parse JSON objects, but storing the data in a database using more primitive types?
You use map-like structures (JSON/HStore, etc) for semi-structured user data that you CAN'T define/know a rigid schema for, ahead-of-time.
Think usescases like: Allowing users to write configuration rules, or lists of custom tag <-> value pairs for (whatever), things of these sorts
We use json columns to store raw data that comes from API responses or structured files. Anything from there we actually use gets copied out into its own strongly-typed column, but the json column remains as a type of logging/tracking as well as the original source of truth for database migrations if we need to pull a new column out, or track down some odd bug.
I've occasionally stored JSON directly in a database. It really depends on what you do with this data. If you do need to query and manipulate the internals of that JSON object then you should extract that data into a proper schema. But sometimes e.g. this is just something the frontend uses and you never (or rarely) have to query the internals, i.e. you treat it as an opaque blob.
