I have never used, and probably will not, but I have to admit, that's incredibly powerful. Super educational information as usual. I think the only thing missing in some videos would be some real-case measurements and testing. For example, there could be two tables: one with JSON elements and another without. We could compare the speed of SELECTs from a million table data. I think this will not only be useful but also very informative about what kind of tables or schemas are more suitable for specific case scenarios. For example It could help us make informed decisions when creating product items attributes in a shop, whether it's better to use JSON or just columns. MYSQL is really powerful ;)
Three minutes into watching this, and I'm already loving the wonderful variety of use cases you have come up with! Feels like a NoSQL approach within a relational db
those videos you make are really great. I worked with mySQL since 15 years ago but I still learn something new and really enjoy the format and your delivery of these topics!
I don`t really often leave comments but you deserved it)) Ngl, I`ve never seen neither more useful, nor "pleasant-to-listen" tutorials in MySQL topic. The way you, Aaron, explain things to us is out of this world, do love it! Keep it up, I`m completely sure many people will find it useful!
Excellent video and resource link to Digital Ocean. I was living in dark ages using MySQL version 5.2x. I upgraded to 8.0.35 and experimented with everything you mentioned in the video. It opened my eyes on how to use MySQL with JSON moving forward. I was using a text field for that before and did not know about all the JSON related functions of MySQL. Thank you very much.
I just stumbled across this video and I haven't finished it but I already love it! I'm still learning about databases but I thought NoSQL was hands down more powerful and scalable than SQL tables, and my previous experience with SQL tables has been a drag, but this is showing me a side of SQL tables I didn't know existed. Thanks!
10 หลายเดือนก่อน +3
I did exactly that in a postgresql db. This is fantastic, much more solid and fast
I'd like to add to this. Some of the reasons for using NoSql are being able to store the data in RAM like using Redis for cache etc and also NoSql is scalable. Both of those also possible in Sql databases. You can definitely configure your db to be stored in the memory and you can definitely have a cluster of Sql database. It is harder to scale up relationships with both Sql and NoSql. NoSql just defaults to having no relationships what so ever. That's what makes it easier to scale. If you do introduce relationships in your data, you'll have the same issues. Needless to say Sql databases are perfectly capable of not using relationships
We often have an "options_json" column where we set configurations of things that we don't directly search often. An example would be on our forms builder, the forms settings someone picks we use to generate the form, or even on questions if a certain question type is required or has visibility requirements, etc. It saves scanning a ton of extra columns.
Right, I had kinda assumed that, while you could, you wouldn't really want to search using JSON data columns. I've just been using it for mostly what he mentioned, metadata, settings, and I like the idea of tenant-specific data structures as I'm currently working in a multi tenant app that we're building out integrations for.
Another idea for JSON data: audit history. For example, don't just have a created_at and updated_at for a user, but also history which contains a log of when the update happened, what changed and who initiated the change.
I almost never touch databases as a frontend, but the videos you make are super interesting. I’m glad you’re giving me a glimpse into my backend friend’s world and hopefully I can help work with them better
Currently using a relational DB for a private project after a while of using exclusively MongoDB in the professional context, and this video has a perfect timing to solve one of the issues I had: having a list of (unknown amount of) tags for any row. Currently I am using extra link tables but a json column would do the job in a more elegant manner imo. Thank you!
I know all of the mentioned JSON function but I really didn't know about array range path and the member of operator, I used json column in many cases but the most useful one was in this scenario: I have 2 tables, one for user notifications and the other for email queuing, each notification/email has a specific template and parameters according to its type and language, the problem is that a user can change his display language any time and he should still be able to read his notifications and emails (that are not sent yet) in the selected language, as you can see here it's not possible to store the notification/email in its final shape, or store it in every possible language, so I stored all the params needed to render the notification/email in a json column and then merge them with the template at runtime. Thanks Aaron! it was a great video as expected 👍
I saw the field type a few months ago and it blew my mind. It felt like everything NoSQL was "known good for" just "vanished". I can just keep using MySQL for most of my stuff :D I still didn't know about some of the things you showed, so thanks
Unreal the quality of content you are putting out. I've made the decision to not use NoSQL db's for certain services thanks in part to this video. Keep it up. Super informative.
I really enjoyed this tutorial. I'm new to DBs as a whole, getting this insight into how JSON-Documents can be handled independently of a NoSQL-DB will surely be useful. I will hopefully be doing a project for changelog-storage in the near future, where this might come in handy!
Json in a MySQL field makes so much sense that I have a hard time imagining people that don't see this as an advantage. Unless you need to store BIG data managing a Json in a MySQL solves so many problems in development and is very flexible if in the future you want to move your Json to a noSQL instead of adding undress of tables and filed and get mad with conversation.
I had my space ship game allow (no require) players to design their own spaceships, and I stored the designs as a json blob. But I that was before I knew it could be a json column rather than a text blob. So this is awesome!
I had fun. This was amazing. THANK YOU. Trying to manage so many EAVs was killing me, and the idea behind that was having a future analytics integration. This is much easier.
In my case I used the JSON column to make sure to get steps that must be followed. This could be changed from task to task, so I realized I could use it instead of just having another NoSQL DB running and waisting resources for this purpose when I can use the same MySQL DB. Of course, this depends on its use and how many times this table will be read and/or written. If iit comes to be a bottleneck for my case I would change it to a single microservice for this purpose but this is far beyond the usage now. Amazing content! Thank you!
sometimes even if a schema is well defined, putting some properties (like additional data with low redundancy and that isn’t used in queries) in a json blob is a good alternative to enormous tables or one to one relationships.
I'm soooo gonna use it. I'm not sure about performance wise, for example, you have 100.000 product rows and you want to query their options or another case what about joins between a json attribute with another table. A use case for that: - again a product row with json column for its options but you don't want to store the actual name of let's say the color but it's id because if you change color value you have to update all the products that contain this color
Great video, thanks! A few months back I watched your full MySQL course and have drafted an improved version for part of our app utilizing a JSON column. We have 3 (currently) types of specification/schema from APIs which we need to store for reference before normalizing. Currently they have all been saved in the same table with column name variations and lots of NULL values for N/A columns, etc 😢 One improvement would be to use separate tables for each type. Another would be to store each raw specification in a JSON object which is what I've experimented with. I've used a dynamic cast on the eloquent model to hydrate the data into the appropriate DTO when fetched. I've also made the JSON column invisible :) Some common high level stats are in the non JSON columns, and the raw data is there if needed. Seems like a tidy solution for our needs.
Great content, Aaron. I have been using the JSON type for quite a long time, but wasn't aware on the index part of that topic (and never was in need of it, yet). Aside from the typical log scenario (API Tx/Rx), I have been using JSON for event stream data, basically the same, but from an event sourcing view on the domain.
Interesting! I am just learning dev and my first foray into it is with WordPress. I know WordPress plugins like Elementor use JSON for the pages you build in them and I wonder if they use this feature of MySQL.
The dollar sign and the array handling are actually part of JSONPath and can be used in jq and other fun tools. I would expect having full JSONPath syntax there.
After playing around with this today. Some useful notes for MariaDB users: 1.) The ->> operator is a shorthand for JSON_UNQUOTE(JSON_EXTRACT()) in MySQL 5.7.13 and later. However, MariaDB does not support this shorthand operator. You're stuck with JSON_UNQUOTE(JSON_EXTRACT(`jsonObject`, '$.your_key')). 2.) When attempting to create STORED or VIRTUAL columns; instead of STORED use PERSISTENT: eg: ALTER TABLE your_table ADD COLUMN your_column VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(`jsonObject`, '$.your_key'))) PERSISTENT; 3.) The INVISIBLE column feature is not supported in MariaDB. It's a feature introduced in MySQL 8.0.23, which allows you to make a column invisible. In MariaDB, you can't make a column invisible directly.
I didn't expect to watch this whole video. You got me excited about JSON. I've already been using it for some metadata columns, stored as TEXT, for a PHP Laravel project . And as we want to use only Db-agnostic query operations, we've shied away from JSON-specific operations that might not be implemented in any major Db drivers we're using. But after watching this I'm going to dig more into the latest Eloquent query builder helper functions as well as possible common raw syntax that might be shared between our supported RDBMs.
Yessss I'm so glad to hear that you stuck around! You've correctly pointed to a good caveat with JSON, it's not exact across all vendors. But! Might as well use the full power of the DB you've chosen, versus sticking to the lowest common denominator. Excited to hear what you get up to!
I think Logging is one good use case for Json blobs, because logs often have specific data related to where this log is coming from, and it's always a pain to work with it in relational dbs. 🤔 Ofc, its mostly for storage, not for constant query. :)
Hey, I was thinking the same. I have met very few Laravel devs and am working full-time with it right now. I love MySQL, but I also love using Eloquent to abstract away all the db-specific syntax, so would love to know if Eloquent could handle some of these JSON functions without restoring to using the `raw` methods.
You’re assuming the reason for using a NoSQL database is wanting to store and index json data. I find often it comes down to scaling writes and data volume.
I am trying to understand 2 points 1. Performance: Which 1 is expensive, Performing JSON operations on mysql or in my backend code 2. Cost: Lets assume we need RDBMS but to store json data if we are using MongoDB then, Is MongoDb will be costly over Mysql if we plan to keep json data into MongoDb instead of Mysql? Please share your expert advice. Thanks for wonderful videos. 😃
The thing is, NoSQL was invented for horizontal scalability, which no RDBMS can allow due to obvious reasons. Handling dynamic JSON objects is merely a bonus.
Vitess has pretty good horizontal scalability! That's the whole reason it exists, in fact. We hit 1m QPS by just adding shards. planetscale.com/blog/one-million-queries-per-second-with-mysql.
@PlanetScale such throughput sounds nice. The question is , what kind of queries are we talking about. How would Vitess handle cross shard joins? And then, I can't help but think on the consistency part of CAP. How does it handle distributed transactions (cross shard)? In such setups, it is critical - does it do 2pc? Or does it do something based on a quorum?
I work with a platform that utilizes the EAV pattern A LOT. It would be so nice to do that json grouping sometimes instead of the millions of joins required to get everything back 😂
You are partially correct, json documents are fully supported in many SQL databases including MySQL But to imply that this was the only advantage of NoSql is wrong Storing dynamic data like json wasn't the advantage of NoSql, the massive advantage of using NoSql is horizontal scalability made a lot easier than any SQL database, that's why you should use NoSql if you need to ever work on apps with large data, don't use MySQL in that case.
You kind of missed an important use case: event sourcing pattern. (although you mentioned storing api calls, fair enough). I’m not sure if MySQL is performant enough for diffing, or creating materialized views from json columns though.
I assume that you, as every person on the planet, would like to retire someday. Against that idea, I'd like to ask you never to stop making videos. I love all of them!
That 'set invisible' option is new to me! Wonder why no one ever suggested to try and set sensitive columns invisible to avoid printing them out by mistake with select * and only getting the sensitive column back when and only when you specifically ask for it. Eg. select email, password from users where id = 1 vs select * from users where id = 1, is it bad practice to use invisible on them? Very good video, top notch!
@@PlanetScale Some things on top of my head: 1. For scalability (concurrent connections) and performance (read/write speeds over single instance and cluster) 2. Why PlanetScale is MySQL instead of Postgres 3. Differences between them, what can Postgres do that MySQL can't and vice versa 4. When to use what Would be great if these are covered!
This info is super useful, at work we are evaluating ditching MySQL for PostgreSQL and get a NoSQL. I know nothing about NoSQL, is it possible to do a second part of this video explaining how these MySQL features compare to NoSQL?
I tried working with json data in mysql, besides the syntax being clunky (compared to postgres) I found it to be super slow compared to structured data, I was processing millions of rows and anything related to json added a significant load on the database sometimes to the point of rendering it useless
Love this content! My use case for the JSON column type is storing additional data on a task table. We have an automation pice in our platform with workflows that contain stages that contain tasks. Each task type can have different data associated with it. Saves me from having a bunch of nullable columns or cluttering up the database with extra tables for task data. I use Zod to validate the inputs as they go into the table so I get the flexibility of JSON and some schema validation.
I wish we had such a great video about JSON on PostgreSQL as well. But clearly I'm saying that on a video by the wrong people to ask about this. PS 30:18 funny how select * took 3ms, but select *, attributes took 1ms. I assume it was just caching, but looked wrong compared to what you said.
It sounds great in theory but in practice It's not the best idea :/. It may work if you don't need to query data inside of the JSON or for some shallowly nested data but if you're not careful then the performance overhead can get out of hand quickly not mentioning serious maintainability problems.
You have been doing really good effort for everyone, it's really nice. I was wondering if you need a video editor team of motionscope, to take your channel to new heights with professional editing.
Amazing video once again! I knew JSON support existed but didn’t know to that depth 😮 Topic suggestion: would love to see a deep dive 30m video on foreign keys: performance, alternatives, when to use vs when too much (ie a massive join table with FKs can be slow inserts), db support (and why?!), cascade behaviors as related to app logic (why null on delete can be bad for app logic), etc. maybe this is covered in your MySQL for devs vid, but can’t remember Also I find these videos to be a gold mine - any thoughts on doing a (cliche but useful) chat bot or similar product to be able to natural language query your video content? I don’t have a use case today for JSON, but might want to search PlanetScale or Aaron’s video catalog to see snippets from all indexed videos and jump to specific pieces to re-watch… not sure if others are asking too but might be worth a
Yeah this is a great great great idea. I need to beef up our content on foreign keys. Added to my list! Also suuuuper interesting idea about a chatbot! Could totally do it, I think
I've been using postgresql for storing jsonb documents for what feels like a decade. Absolutely no need for any other type of `nosql' database. Agree w/ the video here.
SQL is useful when the data model and use case fits. SQL is terrible for network config db's and automation at telco level. At least for that stuff, we only use graph.
Great video, I am just starting to learn mySQL and postgres for work and this is really helpful as we have a JSON object in our database that I've been asked to generate a useful table for. Can I ask what software you are using in this video to write your queries and get the show the table results?
![ตีสิบเดย์ [FULL] | อาจารย์โอเล่ ญาณสัมผัส ผ่าดวงคนดัง ประเทศไทยจะเป็นยังไงหลังจากนี้!](http://i.ytimg.com/vi/gszT2O76fL4/mqdefault.jpg)
This channel pumps out quality database content every week. Definitely deserves more subscribers.
😮💨 I appreciate that
Done!
good point! I just subscribed
Done. Was really a noob mysql Developer hidding behind Eloquent. Now I can review some senior dev codebases. Thanks Franco.
I'm absolutely loving this long-form content, Aaron. So easy to just put each one of these in my `favorites` list. So much value.
Thank you Len! They're a lot of work so I'm glad to hear that
I have never used, and probably will not, but I have to admit, that's incredibly powerful. Super educational information as usual. I think the only thing missing in some videos would be some real-case measurements and testing. For example, there could be two tables: one with JSON elements and another without. We could compare the speed of SELECTs from a million table data. I think this will not only be useful but also very informative about what kind of tables or schemas are more suitable for specific case scenarios. For example It could help us make informed decisions when creating product items attributes in a shop, whether it's better to use JSON or just columns.
MYSQL is really powerful ;)
Three minutes into watching this, and I'm already loving the wonderful variety of use cases you have come up with! Feels like a NoSQL approach within a relational db
Thanks! Kinda crazy right?
@@PlanetScaleright
those videos you make are really great. I worked with mySQL since 15 years ago but I still learn something new and really enjoy the format and your delivery of these topics!
Means the world to me, thank you
I don`t really often leave comments but you deserved it)) Ngl, I`ve never seen neither more useful, nor "pleasant-to-listen" tutorials in MySQL topic. The way you, Aaron, explain things to us is out of this world, do love it!
Keep it up, I`m completely sure many people will find it useful!
What a great start to my week, thank you so much!
This series of videos are incredible, been watching all of them and it really helps me out. Thanks for your work, keep it going.
Thank you! Nice to know it's helping people
Excellent video and resource link to Digital Ocean. I was living in dark ages using MySQL version 5.2x. I upgraded to 8.0.35 and experimented with everything you mentioned in the video. It opened my eyes on how to use MySQL with JSON moving forward. I was using a text field for that before and did not know about all the JSON related functions of MySQL. Thank you very much.
I just stumbled across this video and I haven't finished it but I already love it!
I'm still learning about databases but I thought NoSQL was hands down more powerful and scalable than SQL tables, and my previous experience with SQL tables has been a drag, but this is showing me a side of SQL tables I didn't know existed. Thanks!
I did exactly that in a postgresql db. This is fantastic, much more solid and fast
Dude you're one of the best teachers I've ever run across on the internet or in person. Thanks for what you do. I love your videos.
This is one of the nicest things I've ever heard. Thank you internet stranger, I really really appreciate it
I'd like to add to this. Some of the reasons for using NoSql are being able to store the data in RAM like using Redis for cache etc and also NoSql is scalable. Both of those also possible in Sql databases. You can definitely configure your db to be stored in the memory and you can definitely have a cluster of Sql database. It is harder to scale up relationships with both Sql and NoSql. NoSql just defaults to having no relationships what so ever. That's what makes it easier to scale. If you do introduce relationships in your data, you'll have the same issues. Needless to say Sql databases are perfectly capable of not using relationships
We often have an "options_json" column where we set configurations of things that we don't directly search often. An example would be on our forms builder, the forms settings someone picks we use to generate the form, or even on questions if a certain question type is required or has visibility requirements, etc. It saves scanning a ton of extra columns.
Right, I had kinda assumed that, while you could, you wouldn't really want to search using JSON data columns. I've just been using it for mostly what he mentioned, metadata, settings, and I like the idea of tenant-specific data structures as I'm currently working in a multi tenant app that we're building out integrations for.
Another idea for JSON data: audit history. For example, don't just have a created_at and updated_at for a user, but also history which contains a log of when the update happened, what changed and who initiated the change.
Audit history is a good one! I actually use JSON for that exact use case in an app I built.
I have so many ideas on how I'm going to use this for supporting custom user defined fields in a CRM I'm building! Thank you!!!
Niiiice! Do report back!
Excellent tutorial. Eloquently and clearly explained and demonstrated.
Awesome features at MySql. Thanks for this impressive lesson! Regards from Brazil.
Nice video.
Would be good to see some performance numbers on the queries, specially when using them on predicates
I would love to hear more about subqueries next time, when to use it and when not to use it, as well as the drawbacks.
Nice, I'll do a whole video on that
I almost never touch databases as a frontend, but the videos you make are super interesting.
I’m glad you’re giving me a glimpse into my backend friend’s world and hopefully I can help work with them better
Love to hear that! Thanks for telling me
Currently using a relational DB for a private project after a while of using exclusively MongoDB in the professional context, and this video has a perfect timing to solve one of the issues I had: having a list of (unknown amount of) tags for any row. Currently I am using extra link tables but a json column would do the job in a more elegant manner imo.
Thank you!
Tags are a great use case for sure. Glad it was helpful!
I know all of the mentioned JSON function but I really didn't know about array range path and the member of operator,
I used json column in many cases but the most useful one was in this scenario:
I have 2 tables, one for user notifications and the other for email queuing, each notification/email has a specific template and parameters according to its type and language,
the problem is that a user can change his display language any time and he should still be able to read his notifications and emails (that are not sent yet) in the selected language,
as you can see here it's not possible to store the notification/email in its final shape, or store it in every possible language,
so I stored all the params needed to render the notification/email in a json column and then merge them with the template at runtime.
Thanks Aaron! it was a great video as expected 👍
That's really clever. I like that example, thanks for sharing!
Wow, i been using MySQL all of my career and this video blown my mind. So much value as always Aaron.
Thumbs up for pointing out the nuances, learning this the hard way as senior engineer could be painful...
I saw the field type a few months ago and it blew my mind.
It felt like everything NoSQL was "known good for" just "vanished".
I can just keep using MySQL for most of my stuff :D
I still didn't know about some of the things you showed, so thanks
Unreal the quality of content you are putting out. I've made the decision to not use NoSQL db's for certain services thanks in part to this video. Keep it up. Super informative.
It's pretty nice to have json in sql. Although still figuring out how to do a where in on a json array.
I really enjoyed this tutorial. I'm new to DBs as a whole, getting this insight into how JSON-Documents can be handled independently of a NoSQL-DB will surely be useful. I will hopefully be doing a project for changelog-storage in the near future, where this might come in handy!
Incredible, I wonder how this compares to PG's JSONB🤔
Same actually. What are you most curious about: functionality or performance?
I think when it comes to databases like queries, performance really matters
@@PlanetScale performance benchmarks video would be nice
@@PlanetScaleperformance comparison with json postgres please, also with jsonb
Json in a MySQL field makes so much sense that I have a hard time imagining people that don't see this as an advantage.
Unless you need to store BIG data managing a Json in a MySQL solves so many problems in development and is very flexible if in the future you want to move your Json to a noSQL instead of adding undress of tables and filed and get mad with conversation.
Today(after weeks) I realized that this is an official channel...
I have never seen such a great quality content from an official channel...
🥰 love to hear that.
This video widened my understanding what can be done with MySQL. Thank you !
I had my space ship game allow (no require) players to design their own spaceships, and I stored the designs as a json blob.
But I that was before I knew it could be a json column rather than a text blob. So this is awesome!
Nice! Glad it helped
a clear and practical story, told by a passionate and skillfull db teacher
I love your courses and the way you explain about things. Please keep doing it!
I had fun. This was amazing. THANK YOU. Trying to manage so many EAVs was killing me, and the idea behind that was having a future analytics integration. This is much easier.
Glad to hear it!
In my case I used the JSON column to make sure to get steps that must be followed. This could be changed from task to task, so I realized I could use it instead of just having another NoSQL DB running and waisting resources for this purpose when I can use the same MySQL DB. Of course, this depends on its use and how many times this table will be read and/or written. If iit comes to be a bottleneck for my case I would change it to a single microservice for this purpose but this is far beyond the usage now.
Amazing content! Thank you!
Nice, that's a great use case! Thank you for sharing.
sometimes even if a schema is well defined, putting some properties (like additional data with low redundancy and that isn’t used in queries) in a json blob is a good alternative to enormous tables or one to one relationships.
I'm soooo gonna use it. I'm not sure about performance wise, for example, you have 100.000 product rows and you want to query their options or another case what about joins between a json attribute with another table.
A use case for that:
- again a product row with json column for its options but you don't want to store the actual name of let's say the color but it's id because if you change color value you have to update all the products that contain this color
Managed to get me glued to the screen for more than 30 minutes. Cool video
Yesss that's a feat! Glad you liked it
Great video, thanks!
A few months back I watched your full MySQL course and have drafted an improved version for part of our app utilizing a JSON column.
We have 3 (currently) types of specification/schema from APIs which we need to store for reference before normalizing.
Currently they have all been saved in the same table with column name variations and lots of NULL values for N/A columns, etc 😢
One improvement would be to use separate tables for each type.
Another would be to store each raw specification in a JSON object which is what I've experimented with.
I've used a dynamic cast on the eloquent model to hydrate the data into the appropriate DTO when fetched.
I've also made the JSON column invisible :)
Some common high level stats are in the non JSON columns, and the raw data is there if needed.
Seems like a tidy solution for our needs.
Holy crap, that 'set invisible' bit blew my mind 🤯
Feels like that could be a standalone video huh! Wild right
Great content, Aaron.
I have been using the JSON type for quite a long time, but wasn't aware on the index part of that topic (and never was in need of it, yet).
Aside from the typical log scenario (API Tx/Rx), I have been using JSON for event stream data, basically the same, but from an event sourcing view on the domain.
Mmm I like that. Event stream is a great example.
Didn't know you could create an index on a json array, very useful, and making those blobs invisible is a good tip.
Interesting! I am just learning dev and my first foray into it is with WordPress. I know WordPress plugins like Elementor use JSON for the pages you build in them and I wonder if they use this feature of MySQL.
All you need is one table with one row with one cell to store an entire nosql database. Winning!
Mega brain idea
You are the only guy who derailed the nosql train with already battle tested mature technologies ❤❤❤❤❤
The dollar sign and the array handling are actually part of JSONPath and can be used in jq and other fun tools. I would expect having full JSONPath syntax there.
Oh neat! I didn't know that. Thanks for sharing
Incredibly useful tutorial. Thank you for putting it together.
After playing around with this today. Some useful notes for MariaDB users:
1.) The ->> operator is a shorthand for JSON_UNQUOTE(JSON_EXTRACT()) in MySQL 5.7.13 and later. However, MariaDB does not support this shorthand operator. You're stuck with JSON_UNQUOTE(JSON_EXTRACT(`jsonObject`, '$.your_key')).
2.) When attempting to create STORED or VIRTUAL columns; instead of STORED use PERSISTENT:
eg: ALTER TABLE your_table ADD COLUMN your_column VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(`jsonObject`, '$.your_key'))) PERSISTENT;
3.) The INVISIBLE column feature is not supported in MariaDB. It's a feature introduced in MySQL 8.0.23, which allows you to make a column invisible. In MariaDB, you can't make a column invisible directly.
I didn't expect to watch this whole video. You got me excited about JSON. I've already been using it for some metadata columns, stored as TEXT, for a PHP Laravel project . And as we want to use only Db-agnostic query operations, we've shied away from JSON-specific operations that might not be implemented in any major Db drivers we're using. But after watching this I'm going to dig more into the latest Eloquent query builder helper functions as well as possible common raw syntax that might be shared between our supported RDBMs.
Yessss I'm so glad to hear that you stuck around! You've correctly pointed to a good caveat with JSON, it's not exact across all vendors. But! Might as well use the full power of the DB you've chosen, versus sticking to the lowest common denominator. Excited to hear what you get up to!
Majority of people use nosql for horizontal scalability
Mindblowing 😮, doesen't know any of this JSON functions. Thank you for sharing!
I think Logging is one good use case for Json blobs, because logs often have specific data related to where this log is coming from, and it's always a pain to work with it in relational dbs. 🤔 Ofc, its mostly for storage, not for constant query. :)
14:57 Wow, it even supports the SQL pattern where it's fundamentally undecidable whether you should use singular or plural names.
Some things never change
I really liked your tutorials and content. It feels like I learned new functionalities every single video. Keep it up dude!
Thank you!
Awesome video Aaron. Would love to see one on using these advanced JSON features in Laravel. The documented examples are pretty limited.
Hey, I was thinking the same. I have met very few Laravel devs and am working full-time with it right now. I love MySQL, but I also love using Eloquent to abstract away all the db-specific syntax, so would love to know if Eloquent could handle some of these JSON functions without restoring to using the `raw` methods.
You’re assuming the reason for using a NoSQL database is wanting to store and index json data. I find often it comes down to scaling writes and data volume.
I am trying to understand 2 points
1. Performance: Which 1 is expensive, Performing JSON operations on mysql or in my backend code
2. Cost: Lets assume we need RDBMS but to store json data if we are using MongoDB then, Is MongoDb will be costly over Mysql if we plan to keep json data into MongoDb instead of Mysql?
Please share your expert advice.
Thanks for wonderful videos. 😃
The thing is, NoSQL was invented for horizontal scalability, which no RDBMS can allow due to obvious reasons.
Handling dynamic JSON objects is merely a bonus.
Vitess has pretty good horizontal scalability! That's the whole reason it exists, in fact. We hit 1m QPS by just adding shards. planetscale.com/blog/one-million-queries-per-second-with-mysql.
@PlanetScale such throughput sounds nice. The question is , what kind of queries are we talking about.
How would Vitess handle cross shard joins?
And then, I can't help but think on the consistency part of CAP. How does it handle distributed transactions (cross shard)? In such setups, it is critical - does it do 2pc? Or does it do something based on a quorum?
Awesome first contact with your channel :) thank you for the quality content!
🫡 glad you enjoyed it!
Friendly and kind developer you are, sir
I guess a stored virtual column is for when it is more computationally expensive to recalculate than to load from disc.
I work with a platform that utilizes the EAV pattern A LOT. It would be so nice to do that json grouping sometimes instead of the millions of joins required to get everything back 😂
You are partially correct, json documents are fully supported in many SQL databases including MySQL
But to imply that this was the only advantage of NoSql is wrong
Storing dynamic data like json wasn't the advantage of NoSql, the massive advantage of using NoSql is horizontal scalability made a lot easier than any SQL database, that's why you should use NoSql if you need to ever work on apps with large data, don't use MySQL in that case.
You kind of missed an important use case: event sourcing pattern. (although you mentioned storing api calls, fair enough). I’m not sure if MySQL is performant enough for diffing, or creating materialized views from json columns though.
Ah yeah, nice use case!
really thanks for covering this JSON topic.
Once again, I am in love with databases and planning to be the SQL Chad. Database with this guy, amazing. Great Content, do MORE. 🌟😃
🫡🫡 more on the way
I assume that you, as every person on the planet, would like to retire someday. Against that idea, I'd like to ask you never to stop making videos. I love all of them!
It's not happening any time soon 🫶🫡
been using jsonb for 5 years now, good thing like on postgres, I can index json keys.
That 'set invisible' option is new to me! Wonder why no one ever suggested to try and set sensitive columns invisible to avoid printing them out by mistake with select * and only getting the sensitive column back when and only when you specifically ask for it. Eg. select email, password from users where id = 1 vs select * from users where id = 1, is it bad practice to use invisible on them?
Very good video, top notch!
I'll do a whole video on that, I think it's pretty interesting.
@PlanetScale Can we please get a comparison on MySQL and PostgreSQL? I am still confused why it is so opinionated
What kinds of things would you want to see compared? Anything specific?
@@PlanetScale Some things on top of my head:
1. For scalability (concurrent connections) and performance (read/write speeds over single instance and cluster)
2. Why PlanetScale is MySQL instead of Postgres
3. Differences between them, what can Postgres do that MySQL can't and vice versa
4. When to use what
Would be great if these are covered!
JSON files are not "unstructured" but "semi-structured", text files, videos and sound are "unstructured"
This info is super useful, at work we are evaluating ditching MySQL for PostgreSQL and get a NoSQL. I know nothing about NoSQL, is it possible to do a second part of this video explaining how these MySQL features compare to NoSQL?
been telling people for years to use it, they all went like "but it's an anti-pattern"
me be like "it's a feature"
"And you don't have to use NoSQL as your primary data store. Can you imagine?"
lmfao
😂 Too much shade, or just enough?
The problem is that SQL isn't scalable with millions of requests a minute. NoSQL is.
Not totally true! We've run a million queries *per second* with MySQL: planetscale.com/blog/one-million-queries-per-second-with-mysql
I tried working with json data in mysql, besides the syntax being clunky (compared to postgres) I found it to be super slow compared to structured data, I was processing millions of rows and anything related to json added a significant load on the database sometimes to the point of rendering it useless
I wrote this comment before finishing the video so maybe it's irrelevant
Would you like to talk about discriminated union / sum types in SQL?
So does PostgreSQL. :) I haven't check other RDBMSes, but will not be surprised, if everyone of them has JSON fields.
MS SQL Server certainly does.
Love this content! My use case for the JSON column type is storing additional data on a task table. We have an automation pice in our platform with workflows that contain stages that contain tasks. Each task type can have different data associated with it. Saves me from having a bunch of nullable columns or cluttering up the database with extra tables for task data. I use Zod to validate the inputs as they go into the table so I get the flexibility of JSON and some schema validation.
Ooo nice thinking to use Zod on top of it.
@@PlanetScale for the Laravel folk, you can do the same by casting your JSON column to a DTO. You will have schema and type validation.
Awesome video content as always! Love seeing a video from this channel pop up in my feed ❤
This is so good, you do an amazing job !! It’s crystal clear, we learn a lot, thank you very much ! 👍
I wish we had such a great video about JSON on PostgreSQL as well. But clearly I'm saying that on a video by the wrong people to ask about this.
PS 30:18 funny how select * took 3ms, but select *, attributes took 1ms. I assume it was just caching, but looked wrong compared to what you said.
Wow .... Head Wall! Link to your videos to team tomorrow.
It sounds great in theory but in practice It's not the best idea :/. It may work if you don't need to query data inside of the JSON or for some shallowly nested data but if you're not careful then the performance overhead can get out of hand quickly not mentioning serious maintainability problems.
You have been doing really good effort for everyone, it's really nice. I was wondering if you need a video editor team of motionscope, to take your channel to new heights with professional editing.
We have a Steve the Editor. Thanks tho!
complex topic very well broken down, thank you
Amazing video once again! I knew JSON support existed but didn’t know to that depth 😮
Topic suggestion: would love to see a deep dive 30m video on foreign keys: performance, alternatives, when to use vs when too much (ie a massive join table with FKs can be slow inserts), db support (and why?!), cascade behaviors as related to app logic (why null on delete can be bad for app logic), etc. maybe this is covered in your MySQL for devs vid, but can’t remember
Also I find these videos to be a gold mine - any thoughts on doing a (cliche but useful) chat bot or similar product to be able to natural language query your video content?
I don’t have a use case today for JSON, but might want to search PlanetScale or Aaron’s video catalog to see snippets from all indexed videos and jump to specific pieces to re-watch… not sure if others are asking too but might be worth a
Yeah this is a great great great idea. I need to beef up our content on foreign keys. Added to my list!
Also suuuuper interesting idea about a chatbot! Could totally do it, I think
Plani wrong. The main reason to choose nosql over sql is scale, not data modeling capabilities.
I've been using postgresql for storing jsonb documents for what feels like a decade. Absolutely no need for any other type of `nosql' database. Agree w/ the video here.
Postgres 🤝 JSON 🤝 MySQL
I love this channel so much. Aaron, you are the best
🫶 that means the world to me
Used it for API responses where I might need additional values later on
I just love your videos explaining MySQL ♥
🫶 thank you!
Intresting info... but your glasses m8, do you really have glass in them cause if... they are sooo clean and not reflex free.
What the heck is unstructured JSON? There are rules how a valid JSON has to be. Rules means structure!
The array accessing by index range is kinda crazy, it's like slicing 😊😊
This guy is a good dev rel, kudos
Wow this is great content. I have to do some database queries at work and loving these tips and tricks.
Thank you!
SQL is useful when the data model and use case fits. SQL is terrible for network config db's and automation at telco level. At least for that stuff, we only use graph.
Great video, I am just starting to learn mySQL and postgres for work and this is really helpful as we have a JSON object in our database that I've been asked to generate a useful table for.
Can I ask what software you are using in this video to write your queries and get the show the table results?
It's TablePlus! th-cam.com/video/7V_CJBPZPes/w-d-xo.html