Learning MySQL - JSON in MySQL
Vložit
- čas přidán 5. 08. 2024
- This episode explains how you can now use MySQL to store JSON data natively and also covers a variety of the functions that you can use to manipulate and search JSON data in your tables.
MySQL Course Playlist: • Learning MySQL - Getti...
SQL people table code GIST: gist.github.com/prof3ssorSt3v...
MySQL JSON data type reference: dev.mysql.com/doc/refman/5.7/...
MySQL JSON function reference: dev.mysql.com/doc/refman/5.7/...
JSON website: www.json.org/
Watched all 39 videos. This MAN is a legend!!!
Also nice to pick up some additional tips when your looking for a refresher. Great Video! Thank you.
Bro your way of explaining things is way out of this world.
Hi Steve. Just finished your course. It was awesome! Thank you.
Thank you! great teacher! I was having some problems on how to store arrays in MySQL this video save me a lot of time! Also, your way of explaining things is great! Already subscribed!
Steve, thank you so much for this course. Very informative. People like you run this world
Thank you for this! This was the best and easy explanation I found after checking many other videos on this topic.
One of the most helpful video ever! thanks a whole lot
Thank you so much for the wonderful series. You're such an incredible teacher, and it is so fun to learn!! Best
This has been extremely helpful, thank you very much Steve!
Your channel certainly deserves more subscribers
Great tutorials, really helped me understand SQL better. I know it is late to say, but please add one video of the topic Transaction too.
It's what I am exactly searching for loved your tutorial so much.. ❤️
Thank you for the great course!
Thank you for this nice and useful tutorial.
JSON is very powerful ! If I exagerate, we could have a whole table structure in only one JSON field !
One field and one row
Thanks pal, very useful video tutorial. Go ahead.
Superb presentation!
This is so cool! Thank you very much for this! Great explanation clap clap
Thank you! Very helpful.
great explanation. thank you.
Great!
I am using json data type to extract many APIs to single table (single field).
I don't get careful with data type of what I am extracting it.
Using this approach, I be able to have a big productivity.
I am using too in data system integration.
I don't need to know about data, I only send a Json, of one system to other system, in batch way.
Have a nice day!
Sorry. I don't understand your question.
Great information
thank you - great job
Great tutorial
Thank you Steve, Finished the course, Amazing course.
Feedback: I know it was about MySQL and not about databases, however since you explained from basics (assuming that viewers do not have much knowledge). It would be great if you can make a tutorial on DB design basics. Like foreign key, Integrity normalization and how to normalize. It can be 1 stop shop for databases.
That being said, it was amazing. Thank you.
Thanks. You can post requests in the comments here - czcams.com/video/LCezax2uN3c/video.html
Thank you 👍 it's a great tutorial 🙏👍
its very useful 😍
Hi brother. I love you. Good video !
tanks a lot
Hi Steve. Looks very powerful. Do you have any videos of the examples you mentioned at the end?
I do not have any more videos about JSON in MySQL.
your videos are great keep it up.
may I ask you to make a transactions video, it'd be really helpful.
Please post tutorial requests here - czcams.com/video/LCezax2uN3c/video.html - and vote for ones you want to see most.
Hi! what way to import 300GB sql file in mysql with speed
depends of the MySQL version but you can try with the shell that comes with 5.7.x and 8.0.x
check dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-parallel-table.html
thanks
hey Steve!, what about performance? My use case is storing POST requests as JSON for debugging. Is MySQL recommended for that, or you would rather go for a NoSQL database?
There are no performance issues or differences with saving and retrieving data as JSON in a relational or noSQL database. If you are trying to filter results based on values in the JSON it is a filter on both sides. There can be a slight advantage working with NoSQL because everything is optimized for JSON-like data.
However, in your case ... what else are you doing with the database? Performance is a huge subject with many factors. There are many layers to a web app. Performance needs to be considered at every level.
For example - if you were looking at two different cars and asking which is faster purely based on what tires they have, then you are missing the bigger picture.
@@SteveGriffith-Prof3ssorSt3v3 this particular case is just for QoA, I want to have a table with structured data for incoming post requests where 4 fields are always present, but the json payload varies. This is short lived data (1 week at most) and only for debugging purposes.
I just saw your video and is very helpful. Can you share a link or have you posted any video about json in detail?
I have read MySQL document before coming to your channel. It was tough to read. Thanks
Depends on what you are looking for wrt JSON. I have a few videos that talk about JSON:
czcams.com/video/P2LdhPpqSiU/video.html
czcams.com/video/912_cPllMyg/video.html
czcams.com/video/0k4NwimfszA/video.html
Most of my videos about fetch are using JSON. Node and Express videos talk about JSON too.
Thanks!
i know it's not a big bag of cash but this is what i can afford at this time :( .. thank you, have learned much..
Every cent is appreciated. Thank you.
Hello.
How can I extract a data from a json document and move that data to the column of a table. From a Server I get information in Json format, but I only care about one piece of all that document in json. I would like to move that single data to a column of a created table.
That would completely depend on what server-side programming language you are using. It would be in your programming that you extract the data from the JSON and then put your value into your database, in a column of the proper datatype.
Thanks for the tutorial. However, i have a question. I am using the most recent version of mysql with an innodb table and neither -> or ->> work. It displays a syntax error.
I don't understand what is happening.
Hi Same with me.
I am also facing same problem
Please help
Great explanation, but my question is when to use json to store data over traditional way of inserting single value in the column ? Say
if I have a bunch of medical data related to a patient (say 100 items) for example should i store those data in json object as it is very hard and seems inlogical to create a column for each property
If the data is originally in JSON or you always want to retrieve the data as JSON then use that datatype. It's really just the same considerations for all datatypes. Store your raw data in the database. Formatting like currencies should be applied at the user-agent level. Data manipulation and calculations can be in stored procedures, stored functions, SQL queries, views, or entirely in your business logic. There is no one answer that answers all situations.
Could you provide a video where we search like a where in? so we search rows where they etc contain a hashtag which is stores as hashtag: ["hashtag1", "hashtag2", "hashtag3"]
I have a video in this playlist on filtering with IN vs equals czcams.com/video/kEtPscGuSso/video.html
Nice video. Thanks! Good way of explanation.
We're trying to refactor our project and we are now discussing the database structure. We're trying to research about json vs. the "normal" way of mysql (cols, 1:n, n:n, and so on).
What the problem is, we do not know what way is better.
Let's say you have a row with 30 cols as table with various datatypes. If we do the same thing with json - which one will be faster for our database? I think Performance is a big topic here. Can you say something to this?
Like.. When does Json outplay Cols? Or when do Cols outplay Json? Or is it just the same effort for the database driver?
I think JSON should be more performant, because you dont store NULL or empty columns. If you have a "normal" table it is possible that some rows have NULLs in there. That's an unnecessary way. That would be a big PRO for JSON.
Is it good to mix up?
Like if you have a user-table with your example. Would it be better if we set only the personal_data in JSON or the whole user-row?
I know.. big questions. But I think you have the knowledge and experience to these questions ;)
Greetings from Austria
If I'm using an RDBMS then I'm going to want to normalize my data and keep things in columns and rows. If I have a small amount of data that is being sent to my server as JSON then I would definitely consider using the JSON data type to hold that data. Modifications and filtering of that data can be done but I'm still going to be relying on my relational table structure to do the heavy lifting.
If I want to work with a lot of JSON data then I would seriously consider using a document database system.
The two different DB types require a different way of thinking about the data as well as the architecture of your application. Both have pros and cons.
Don't try to force your data or your system to work with the different paradigm just to say that you can or that you are.
Could you please make video for JSON usage in triggers and converting JSON data to a table record
Triggers are driven by constraints not data types. Json is a datatype to be used in columns, just like other datatypes. When planning your database you chose how to store your data - as json or as separate values.
Conversions between JSON and something else is done in your code - php, nodejs, python, etc, not in the database.
JSON_EXTRACT(data, \"$.STUDENT_NAME\") IN ('JASMINE','SAM') this is not working? any ideas?
Can I use these json-extensions in SQL commands in PHP PDO?
As long as you are using the right version of MYSQL database
Above video is the best explanation of json in MySQL. But I have a question here, how can we compare 2 json object by using MySQL? For example I have a json object {"id":123, "name":"Swagat"} and after that same object's name has been changed like this {"id":123, "name":"Ankit"} by comparing these 2 object i should get the updated value ? Is that possible ?
This method dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains can be used to compare JSON objects.
When I tried inserting a JSON field it saves as LONGTEXT. As of now with XAMPP we get mariaDB and not Mysql anymore. Please suggest how can I use Mysql or Insert JSON in MariaDB. Any help would be greatly appreciated. Thanks Steve.
LONGTEXT is an alias for JSON in MariaDB - mariadb.com/kb/en/json-data-type/
MariaDB has the same basic functions as MySQL for JSON - mariadb.com/kb/en/json-functions/
@@SteveGriffith-Prof3ssorSt3v3 Thanks a lot.
Example for nested JSON.
set @dir = curdate();
select @dir;
select
json_pretty(json_object("Name","Kangna","DOB",1990,"Language","Hindi","Country","International","Date",@dir
,"Hobbies",json_array("Swimming","Dancing"),"Misc",json_object("Cars","Merc","Zodiac","Scorpion")));
How to add json array into MySQL using nodejs? I want to insert keys and values separately
Communication between MySQL and nodeJS is a whole playlist in itself.
As for JSON in MySQL, why would you separate the keys and values. JSON objects are intended to be self contained documents. We put the whole document into the database because the database does a good job storing chunks of data and retrieving them. NodeJS gives MySQL a whole JSON document and then that whole document is saved in a single field in the database. MySQL will use a primary key on the row to reference the document and to be able to return it to our node script. the NodeJS script is the one that parses and works with the JSON document.
@@SteveGriffith-Prof3ssorSt3v3 Thanks for your replay, well am actually trying to build somthing like this conversion tool sqlizer.io/json-to-mysql/#/
so that am trying to insert data separatly to ease the conversion process, i succeeded in storing keys and valyes as array in mysql table; BUT am having problem with nested values..
Hi again, Steve. May I know why the -> isn't working for me? However, if I use the JSON_EXTRACT (see below), it works perfectly fine.
// This works perfectly.
SELECT * FROM table WHERE JSON_EXTRACT(column_name, "$.email") = "some@email.com"
// But not this. This one gives me a syntax error.
SELECT * FROM table WHERE column_name->>"$.email" = "some@email.com";
The column_name has data that looks like this:
{ "name": "name of person", "email": "some@email.com" }
Yeah I am also getting same
Q: JSON vs another table for a relationship? This seems like a great use case for something like a user that has more than one phone number or email. Typically I would associate this with perhaps another table or add using a | to delimitate this in the field. So this seems like the best of all worlds almost like having mongo inside of mysql to a degree no? Are there any downsides to using JSON in mysql? that you can think of? Guess parsing this if it was complex? Thanks as always Steve!
In a relational DB I would recommend limiting JSON fields to objects that are used in the client side like user objects or for tracking sessions like JWT tokens.
The power of relational DBs is the fact that they maintain the relationships between tables for you. If well design it will be a very efficient way to store and parse data. The support for JSON is just because JSON data is so widely supported on the web.
If everything you are doing in your site is working with JSON then it is worth considering switching to a NoSQL document database.
Great tutorial . I have a question .
Can I have instead json objects , like this - { index: 0, name: 'john' }, { index: 1, name: 'bob' },
can I have a list of arrays ? something like this - ['john', 'Bob'] ?
You can use any valid JSON string.
May I know how to retrieve a key value pair inside a JSON? I meant like if the JSON data looks like this: { a:1, b: { c: 2, d: 3, e: { f: etc }}} How do we access those keys inside?
dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-keys
@@SteveGriffith-Prof3ssorSt3v3 I have read the link but I don't know how to use it in conjunction with PHP. For example, the JSON_SEARCH example in the link uses @json. But when working in PHP, I would need a table instead of the @json variable. Also, I did try using JSON_KEYS in PHP (putting it as a quoted $sql statement) but I couldn't make it work. For the examples you gave, it sort of worked but I dont understand YET how to use this with PHP.
When using PHP with JSON, treat it like a String. JSON in MySQL is just a column that holds these special strings. The JSON functions are so you can run a query filter based on keys or values in that data.
OR just bring a simple filtered dataset back to PHP and do the JSON manipulation there.
@@SteveGriffith-Prof3ssorSt3v3 thanks for replying and the tips! 👍🏻👍🏻👍🏻 I shall study them. Keep making them videos 😊
"{something:{something:[this:that]}}": How can I write a SQL query in order to obtain "that" from this JSON array?
With the JSON_EXTRACT method that I showed in the video @6:58
I must've watched 50 videos this week and followed 10 tutorials on SQL, Fetch, JSON data - but not a single one shows you how to connect to the server. Somebody please help!!
What server? What connection? Web Server? Database server? From client-side or server-side? What server-side language? You need to be clear about what you are trying to accomplish.
HTML loads a JS file. A JS file can use fetch( ) to make an HTTP request to a web server and request a specific resource. The resource on the web server can be a static file or a script that runs when the HTTP request arrives. The server-side script can be written in one of dozens of different languages. The server-side script can connect to a database server, but again, there are many many different types of databases and different types of connections for each. It's not just a single line of code that is being omitted from all these tutorials.
@@SteveGriffith-Prof3ssorSt3v3 Nice explanation Prof3ssor St3v3
Sir iam 8:31 using exact query in mysql but it is showing #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>>'
SELECT modules,
modules->>"$.role" AS role
FROM `user_permission`
This is my Query and my column doesn't taking JSON data type it always set it as longtext
MariaDB diverged from MySQL when it was created. It is likely going to have different methods and syntax. The ->> syntax is likely one of these differences. I don't use MariaDB so I don't know the exact syntax. You will have to check the MariaDB documentation to find it.
@@SteveGriffith-Prof3ssorSt3v3 i dont know sir how to thankyou this video is around 3 years late and u replied not hoping that you will reply sir but ehich database are u using ???
Yes i know today only that mariadb and mysql is different so i check8ng out the codes
Iam using codeigniter 3 but whenever i fetch data i always got response in single backslash but in mysql it shows data without backslash
I want to remove it i dont know why it is showing like that
@@thelatelateshowwithmaaz6260 I use MySQL not MariaDB.
Backslashes are used in front of special characters like quotation marks as escape sequences.
JSON_ARRAY({"name", "John Doe"}); possible?
dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html#function_json-array
yes.
but sir, how can I store form input data here?
With a server side script written with PHP or Ruby or NodeJS or C# or Python. The browser submits the form to the server. The server saves the data in the database by running SQL commands
@@SteveGriffith-Prof3ssorSt3v3 thank you. I know little bit php, but how can i store form data using javascript(json) and php? please make some videos about JSON and php.
Good video but how this json filter scan will work on 3 Billions records ?
Same as any MySQL query that you run on 3 billion records.
But how to insert into a JSON object?
JSON is a string. You can use the various MySQL JSON methods to manipulate it.
,@@SteveGriffith-Prof3ssorSt3v3 I mean updating it add a key-value, delete a key, array push-pull, pop, etc.
Thank you for replying.
Hi, How are you? Please, help me!!!
I am trying to do it, but it doesn't works for me.
My consult: SELECT desktop_access->>"$.username" as name FROM v_components WHERE desktop_access->>"$.username" = "facu"
My array for each object on column "deskto_access":
[
{"username":"santi","x":500,"y":25},
{"username":"facu","x":500,"y":25}
]
Error with "->>"
It is a fairly new thing. Check your version of MySQL. It must be 5.7.13 or higher.
@@SteveGriffith-Prof3ssorSt3v3 It is Versión del servidor: 10.4.8-MariaDB - mariadb.org binary distribution
I have same same problem
Hello dear Mr Steve, I created a program by Cordova, but I need digital signing to publish it, but I can not do it. Please help me, my project is done in Visual Code.
Sorry but I have not done any videos on signing apps
@@SteveGriffith-Prof3ssorSt3v3 Please, thank you for answering
EAV is dead and JSON killed it.
It still has an important place in the world. JSON just led to document databases and a different solution to web style problems.
A dislike from xml team...
XML definitely has its place but for web developers JSON is almost always the preferred option