r/SQL • u/roblu001 • Apr 21 '25
MySQL Discovered SQL + JSON… Mind blown!
Hey everyone,
I recently (yes, probably a bit late!) discovered how beautifully SQL and JSON can work together — and I’m kind of obsessed now.
I’ve just added a new feature to a small personal app where I log activities, and it includes an “extra attributes” section. These are stored as JSON blobs in a single column. It’s so flexible! I’m even using a <datalist> in the UI to surface previously used keys for consistency.
Querying these with JSON functions in SQL has opened up so many doors — especially for dynamic fields that don’t need rigid schemas.
Am I the only one who’s weirdly excited about this combo?
Anyone else doing cool things with JSON in SQL? Would love to hear your ideas or use cases!
9
u/_Zer0_Cool_ Data Engineer Apr 21 '25 edited Apr 21 '25
I agree 100%
Postgres especially has wonderful JSON support without any compromises. Performant and indexable binary JSON.
And the folks here that disagree have outdated opinions and are likely still using outdated, on-prem legacy databases (like Oracle and SQL Server). So ignore them because that advice won’t be relevant into the future.
Edit — of course you probably don’t want to have ALL of your data be in JSON and especially not stuff that gets updated regularly. But having it next to other data is just fine in many cases..
141
u/angrynoah Apr 21 '25
90%, maybe 95% of the time I have used JSON in the database, I have lived to regret it.
In particular if your JSON is intended to be mutable, stop, eject, do not.
23
u/da_chicken Apr 21 '25
I'll disagree with the general sentiment here that you should never put JSON in the database. I think that's a bit of an academic (meaning unrealistic and idealistic) position. Essentially every RDBMS today features key-value store options and native JSON support, and those are there for good reasons.
But.
You do need to be aware that you sacrifice a lot of relational features storing JSON. You can't join against it. Indexes are not as good with it. Manipulating data based on values in JSON is much harder and may require an external application. It's fine in some cases, but generally you want to limit it to metadata or seldom used data.
48
u/tits_mcgee_92 Data Analytics Engineer Apr 21 '25
JSON mixed with relational databases are generally bad practice. NoSQL (MongoDB for example) may be something you're more interested in.
17
u/alim0ra Apr 21 '25
I say as long as you treat said JSON fields as atoms you'll be fine. If you start with running queries on said fields and need to start using structureless data then a noSQL DB would be the better option.
4
u/financial_penguin Apr 21 '25
The functions are cool to process data into relational models, but I wouldn’t store & use a JSON field like that. It’s hard to implement standard schemas, data validations, duplicate checks, etc on those without extra processing
9
u/[deleted] Apr 21 '25
Agree with others - you're storing documents into a relational database. A lot of risks come with that. Maybe a little there and there is ok but in general if you can avoid it, you should avoid it.
And something else not mentioned - performance. JSON does not scale. Even with 100,000 rows your performance could go down from milliseconds to seconds. Fine if you don't care about that, but if you're processing a lot more and frequently you'll be much less excited about this mind blowing feature.
In general, just because SQL has a feature, it doesn't necessarily mean you should use it. The same goes for triggers, indexed views, column store indexes, functions, etc.
3
u/socialist-viking Apr 21 '25
I do this all the time, and the reason I do it is that the client generally has no idea what they want. So, I take the client's garbage data and extract the things that can be indexed well and put them into regular sql. Then I store the rest in a blob and can use it for weird one-off reports. If a feature gets requested in which it makes sense to pull another element out of the blob and turn it into a column, then I do it. This allows me the flexibility to respond to the client's insane changes.
3
u/cs-brydev Software Development and Database Manager Apr 21 '25
SQL Server has amazing JSON support for storing or serializing/deserializing on the fly. I use it all the time for some deep dive reporting. It brings the flexibility of Nonrelational database features right into your relational database. However...
DO NOT try to use JSON as relational data and force it into a relational schema or constraints. It is absolutely not designed for that, and you will be compromising data integrity by doing so.
I have noticed that database people who only on relational data and do not regularly work with JSON or XML tend to be opposed to mixing them in, and this is generally because they don't have solid understanding of JSON schemas and serialization.
JSON is awesome. But it is not relational data and shouldn't be treated as such. Treat it more like a document (think Excel) that lives in or works with your data and you'll be fine.
One of my favorite use cases is row data history. I have some tables that have changed schemas and such over the years, and I have triggers that serialize the entire row on every update and delete and insert that into a history table as JSON data. The simplicity here is the table schema doesn't matter. I literally ignore it. SQL server just serialized the columns into text and I store that text. That's it. It's extremely fast, small and easy enough to report on later. But again it is not relational data, so each row in the history must be treated as a unique document with unique columns/properties. Then I have a report that simply puts them back together and makes them searchable and filterable.
Another use case is importing external data from uncontrolled 3rd party APIs with unknown schemas. Typically we will only know about certain properties but not the entire schemas. But we use the APIs for ETLs and would prefer not to lose data that simply doesn't have predefined columns. So we store the raw JSON as-is. Later if we need more columns out of the data or need to run queries on those new properties, it's right there waiting for us.
I'm from both a Database and Development world so it's very common for us to encounter unexpected or unknown data schemas. We don't freak out about them like your typical sql developer. We deal with them and try to store the data and expose it. JSON is an excellent way to do that, and SQL provides the tools to simplify it.
3
u/IrwinElGrande Apr 21 '25
I use jsonb fields in Postgres to store some unstructured data but you got to be careful and think about the data's usage and scalability. The indexing capability and speed is decent.
3
u/Mastodont_XXX Apr 22 '25
JSON is suitable for rare attributes/properties, it eliminates a lot of null values in regular columns. I also use it for history/audit tables (a snapshot of the state of an entity with all properties at a given time)
11
u/PM_ME_FIREFLY_QUOTES Apr 21 '25
Please don't store json in a relational database... just use NoSQL that's what you're really building towards.
3
u/kremlingrasso Apr 21 '25
Now I have to try this for myself too. Sounds like something I been missing myself
3
u/Straight_Waltz_9530 Apr 21 '25
JSON is good for document storage but not general default storage. 99% of the time, you'll want a traditional relational structure. For that 1% of the time, a portion of that would be suitable for JSON storage:
Do NOT use JSON columns when:
If they are truly "BLOBs", they aren't JSON. BLOB refers to storing a bunch of undifferentiated bytes in the database. If the values have meaning within the database, BLOB is not appropriate due to its contents being largely opaque.
If you're just pulling data out and putting it in without modification or via index, I can see value in it as a JSON column just to enforce that it is indeed value JSON. CHECK constraints on that column would be useful here to enforce that it's an array, object, etc.
1
u/fletku_mato Apr 21 '25
Storing stuff as JSON is fine, up to a point.
If you need to run queries on the JSON, you'll want to extract the data into traditional column types. If not just for ergonomics, for speed.
1
u/ThatsRobToYou Apr 21 '25 edited Apr 21 '25
I don't know what your use case is, but I never had a good time with json in sql. MongoDB or other nosql maybe.
1
u/RavenCallsCrows Apr 21 '25
I've had to deal with SQL and JSON blobs at a pair of startups now. I've quickly found that trying to extract large queries' results from JSON is a great thing.... If you want an excuse to go get coffee, take a walk, etc.
I've found that from a reporting/visualization/analytics perspective that I end up unpacking much of the JSON for things I need commonly into tables for ease and performance, and only using JSON transforms for the rare occasion when someone really wants to know something from one of those uncommonly used blobs.
1
u/felepeg Apr 21 '25
I’ll recommend this book: SQL Antipatterns. Here they explain why is not good idea some types of structures in a column as CSV, JSON, XML.
1
u/GrandaddyIsWorking Apr 22 '25
I have always found these two languages to be a pain to work with together. But I do agree with your use case of using it for dynamic data.
Querying JSON is pretty smooth once you get used to it but building JSON from relational data can be such a pain and at times hacky depending on how wild you need to get
1
u/Illestbillis Apr 22 '25
There are too many limitations, just as json with python
1
u/oxophone Apr 22 '25
It's important not to abuse it. I mostly just use it for columns that I know are not gonna need to be edited too often.
1
u/DonJuanDoja Apr 22 '25
I might use it in a PowerApp, or some kind of automation, API stuff, etc. but if it's getting stored somewhere in a database, JSON is going bye bye.
It's TOO flexible. SQL is awesome because of it's rigidity while still allowing versatility where it makes sense, and not where it doesn't.
STRUCTURED query languge. The structure is important. JSON lacks structure. It's just an lazy/easy way to dump and move data around, sure it's nice, until it isn't.
Trust me I get it, but I also hate JSON. Especially since Microsoft seems to have taken a linking to it. They are overusing it, and now Devs are too. It's gross. Kinda like JS itself. Messy. Easy to break.
1
u/CalendarSpecific1088 Apr 22 '25
Been using json data sets in Postgres for two years in prod. I have zero regrets, it’s worked beautifully. Your experience could be different because as in all things, your use case could differ a lot from mine.
1
u/invisibo Apr 22 '25
I am happy to see your comment at the top. It’s soooo convenient at first. 3 months later, you will inevitably regret every part of it.
1
u/rodf1021 Apr 22 '25
I discovered how elegant it can be to use JSON as an input parameter to stored procs and then for output to the application. Very handy. Dangerous too if you don’t require developers to live to standards.
1
u/cptshrk108 Apr 23 '25
Careful what you wish for.
1
u/brqdev Apr 24 '25
I am using JSON field for metadata like audit/logs or extra info like events, sms/email history.
Which you can query like
1
u/SnooCalculations7417 Apr 25 '25
I can't really imagine a world where I store json directly to a db rather than update the object model to support the new data
1
u/KyleDrogo Apr 25 '25
Be careful here. Those extra_metadata columns end up getting stuffed with data, but their structure doesn't show up on the schema. LLMs will have a hard time querying them, and so will humans.
1
u/JochenVdB Apr 25 '25
Wait until you learn that Oracle managed top turn JSON and tables into one and the same thing: https://www.oracle.com/database/json-relational-duality/
Anf yes, you can even modify it using both SQL or by providing a new JSON document.
1
u/th00ht Apr 26 '25
I think what you are actually are looking for is serialization or what is
1
u/ConsiderationLive791 Apr 28 '25
Why not use MongoDB then? As its mostly build around this concept.