MySQL, JSON, & You: Perfect Together

Long before 'NoSQL' databases became all the rage, developers stored JSON in relational database tables. With the advent of the JSON data type, developers now have a powerful set of functions to update, retrieve, and filter data based on values stored in a JSON blob. In this session, we discuss storing JSON in our database and how the tools in MySQL can make that task a bit easier. We will start with a discussion of why you might want to store data as JSON as opposed to other storage methods. We will then talk about the different ways in which MySQL can help us achieve that goal and how we might go about deciding what method is best. We will see examples of using the JSON data type to store data in a 'normal' table and use SQL commands to retrieve filter, and sort that data. We will also show how you can validate your JSON schema.

Transcript

1. MySQL, JSON, & You: Perfect Together Storing and Retrieving JSON Data in MySQL Scott Stroz MySQL Developer Advocate
2. • MySQL Developer Advocate • Full-stack developer for longer than the term has existed • The only constant in my stack has been MySQL • I like to give things away! • Former paramedic • Have delivered three babies • I have the best officemate in the world. Obligatory "I Love Me" Slide 3/13/24 Copyright © 2021, Oracle and/or its affiliates 2
3. • What is JSON? • Storing JSON as a STRING as opposed to JSON • Why store JSON? • Persisting JSON data • Retrieving JSON data • Updating JSON data • Using relational data as JSON • Indexing JSON data What will we discuss? 3/13/24 Copyright © 2021, Oracle and/or its affiliates 3 Photo by Ferenc Almasi on Unsplash
4. What is JSON? 3/13/24 Copyright © 2021, Oracle and/or its affiliates 4 Photo by Markus Spiske on Unsplash
5. What is JSON? 3/13/24 Copyright © 2021, Oracle and/or its affiliates 5 Photo by Markus Spiske on Unsplash • JSON – JavaScript Object Notation • Textual representation of a data structure • Objects are wrapped in {} • Properties are key-value pairs • Keys, strings, and dates must be wrapped in " " • Numbers, Booleans, and null are not • Arrays are wrapped in [] • Data can be nested • Language independent.
6. JSON Syntax 3/13/24 Copyright © 2021, Oracle and/or its affiliates 6
7. • Faster development time • If schema will change often, it might be better to have no schema • Less verbose than XML • Pretty much every programming language can ‘read’ JSON • Some data is unstructured • Configuration data • Exception Logging • Data from 3rd Party APIs • When you cannot control the schema Why Store JSON? 3/13/24 Copyright © 2021, Oracle and/or its affiliates 7 Image by Tumisu from Pixabay
8. JSON as STRING vs. JSON as JSON 3/13/24 Copyright © 2021, Oracle and/or its affiliates 8 • It was used long before the JSON data type existed • Stored as CHAR, VARCHAR, TEXT, etc. • Searching by values required the use of LIKE or REGEXP • Updating any value of the JSON object would require rewriting the entire string • Introduced in MySQL 5.7 • Designed to hold valid JSON documents. • Stored in a binary format • Optimized for replication & quick searches • Can have a defined schema JSON as STRING JSON as JSON
9. Let's Play "Valid or Not"! 3/13/24 Copyright © 2021, Oracle and/or its affiliates 9 Valid!!
10. Let's Play "Valid or Not"! 3/13/24 Copyright © 2021, Oracle and/or its affiliates 10 Valid!!
11. Let's Play "Valid or Not"! 3/13/24 Copyright © 2021, Oracle and/or its affiliates 11 Valid!!
12. Let's Play "Valid or Not"! 3/13/24 Copyright © 2021, Oracle and/or its affiliates 12 Valid!!
13. Let's Play "Valid or Not"! 3/13/24 Copyright © 2021, Oracle and/or its affiliates 13 Not!!
14. Creating a Table with a JSON Column 3/13/24 Copyright © 2021, Oracle and/or its affiliates 14
15. Inserting JSON 3/13/24 Copyright © 2021, Oracle and/or its affiliates 15
16. Inserting JSON 3/13/24 Copyright © 2021, Oracle and/or its affiliates 16
17. json_keys() – Returns an array of the keys of a JSON object. Helper Functions 3/13/24 Copyright © 2021, Oracle and/or its affiliates 17
18. json_pretty()– Returns JSON in easier to read format Helper Functions 3/13/24 Copyright © 2021, Oracle and/or its affiliates 18
19. Using Path Operators 3/13/24 Copyright © 2021, Oracle and/or its affiliates 19
20. Using Path Operators 3/13/24 Copyright © 2021, Oracle and/or its affiliates 20
21. 3/13/24 Copyright © 2021, Oracle and/or its affiliates 21 Querying Data Based on JSON Values
22. json_contains() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 22
23. json_contains() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 23
24. json_contains() (with strings) 3/13/24 Copyright © 2021, Oracle and/or its affiliates 24
25. json_contains() (with strings) 3/13/24 Copyright © 2021, Oracle and/or its affiliates 25
26. Same query using path operators… 3/13/24 Copyright © 2021, Oracle and/or its affiliates 26
27. Same query using path operators… 3/13/24 Copyright © 2021, Oracle and/or its affiliates 27
28. 3/13/24 Copyright © 2021, Oracle and/or its affiliates 28 Querying JSON Array Data
29. json_table() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 29
30. json_table() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 30
31. 3/13/24 Copyright © 2021, Oracle and/or its affiliates 31 Updating JSON Values
32. • Inserts a new key to a JSON document • Will NOT update the value for existing keys • Can add multiple keys in a single statement • Updates values to existing keys in a JSON document • Will NOT add the key if it does not exist • Can update multiple keys in a single statement • Inserts and updates values in a JSON document. • If the key exists, the old value is updated. • If the key does not exist, it is added, and the new value is used How do we update key-value pairs? 3/13/24 Copyright © 2021, Oracle and/or its affiliates 32 JSON_INSERT() JSON_SET() JSON_REPLACE()
33. json_insert() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 33
34. json_replace() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 34
35. json_set() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 35
36. json_remove() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 36
37. 3/13/24 Copyright © 2021, Oracle and/or its affiliates 37 Returning non-JSON data as JSON
38. Using json_object() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 38
39. Using json_object() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 39
40. Using json_arrayagg() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 40
41. Using json_arrayagg() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 41
42. Copyright © 2021, Oracle and/or its affiliates 42 Indexing JSON Data 3/13/24
43. Basic query of JSON Data 3/13/24 Copyright © 2021, Oracle and/or its affiliates 43
44. Basic query of JSON Data 3/13/24 Copyright © 2021, Oracle and/or its affiliates 44
45. Creating an Index for JSON properties 3/13/24 Copyright © 2021, Oracle and/or its affiliates 45
46. Checking new index 3/13/24 Copyright © 2021, Oracle and/or its affiliates 46
47. Checking new index 3/13/24 Copyright © 2021, Oracle and/or its affiliates 47
48. Recheck the Explain Plan 3/13/24 Copyright © 2021, Oracle and/or its affiliates 48
49. Recheck the Explain Plan 3/13/24 Copyright © 2021, Oracle and/or its affiliates 49
50. MySQL JSON Function Reference Resources 3/13/24 Copyright © 2021, Oracle and/or its affiliates 50
51. Copyright © 2021, Oracle and/or its affiliates 51 Q&A Thank You! 3/13/24