Newbie here, is storing json in sql (as like column data) as weird as I think it is or are there valid use cases?

The one I heard, didn't get the details but something like "startup move fast"

Weird. Json stuff is usually in files. I see no advantages of having to query a database to get some values except of course if you deliberately want to be slow.

Not weird, for us. We store meta-data regarding delivery restriction details that don't fit a traditional normalized sql server data structure. De-normalized about a dozen tables into a single field sped up queries significant enough to warrant the very minor hit de-serializing of the json string.

The shit I have seen...

JSON is not even in the top 50 weird things I have seen.

@PaperTrail You're telling me that database calls can be faster than parsing json? My life has been a lie

JSON is usually faster to query and parse then a query over a n-to-n join on 2 tables (5+ joins on the same table for different indexes kind of BS) to stick all the data back together.

Storing and querying JSON is usually a bad sign that the DBA doesn't understand relational databases, but there are some benefits to it.

@Ranchonyx > "You're telling me that database calls can be faster than parsing json?"

No, no, no....the meta-data is never queried directly, its part of the payload to be interpreted by the client/UI. Our DBA would take away someone's keyboard if they tried to hit/query that field directly.

@PaperTrail Genuinely had me worried there for a moment

There are plenty of cases where it's acceptable. It can for example be a sensible read performance optimisation for complex models. (Hierarchical parts of the model that, rarely changes, needs to be read but is never queried can be collapsed into a single blob to eliminate expensive 1 to n joins).

In most such cases however the better option would be to use a different database.

@Ranchonyx > "Genuinely had me worried there for a moment"

Its not like the design was version 1. I think we're around #6 now.

Started out as 2nd/3rd normal form, then the queries were impossible to get performant enough, then storing the meta-data as XML was a night-n'-day difference in performance/usability/expand-ability.

New DBAs right out of college had fits about storing XML ("worst thing I ever saw...XML is slow...", etc etc), so they attempted to normalize again (there were at least a dozen tables) and failed attempting to get the same performance+behavior (after a few versions/variations)

TL;DR, the structure is now nearly identical to what it was 15 years ago except the Details field contains JSON instead of XML.

I use sqlite dbs as local files and store all sorts of shit in them. It makes more sense to use a db as a file format than to create some proprietary file format nothing other than my app can read. That way my clients can actually use an sqlite db browser to extract data if they have to. I have a file right now I am using to collect data over hours and it grows to be quite large. 200 to 300 Mbytes over a day. However, it allows me to recreate an entire day of collecting data to improve the positioning algorithms later.

TLDR - sqlite is a great file format for local data including json data.

Yes there are valid use cases for it.
A lot of these usecases can be resolved differently by
1. Storing the JSON as a file and linking it to the record.
2. Creating a property/value table to pivot the json priorities to rows.

JSON is flexible. If you don't know what columns to expect your can't make a schema beforehand. The above two methods can do the same but can be less convenient. 1. Does not allow for querying the JSON and the storage might not be strong consistent. 2. It's slow to read back into JSON (especially with complex values)

PostgreSQL specifically has a JSON(B) column type that can be queried.

It definitely IS weird, but it's also way more common than you think.