MySQL Document Store: How to use MySQL without SQL

Yes, it's possible to use MySQL without a single line of SQL.

Join this session to discover how MySQL, thanks to the X dev API, allows you to store and manage JSON documents like any other Document Store.

You will also discover how you can benefit from the best of both worlds, RDBMS and NoSQL in one single database.

Finally, you will also see how it's possible to use this in the Cloud using MySQL HeatWave in OCI and discover the very new MRS, MySQL REST Service!

This session was proposed at a MySQL Meetup in Bratislava.

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL Czechia & Slovakia Tour - March 2025 MySQL Document Store: how to use MySQL without SQL MySQL for Developers
2. Who am I ? about.me/lefred Copyright @ 2025 Oracle and/or its affiliates. 2
3. • @lefred • @lefredbe.bsky.social • @lefred@fosstodon.org • MySQL Evangelist • using MySQL since version 3.20 • devops believer • living in • h�ps://lefred.be Frédéric Descamps Copyright @ 2025 Oracle and/or its affiliates. 3
4. MySQL 8.0 / 9.0 Document Store discovery of a new world Copyright @ 2025 Oracle and/or its affiliates. 4
5. NoSQL Document Store • Schemaless Copyright @ 2025 Oracle and/or its affiliates. 5
6. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... Copyright @ 2025 Oracle and/or its affiliates. 5
7. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... ◦ very quick initial development Copyright @ 2025 Oracle and/or its affiliates. 5
8. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... ◦ very quick initial development • Flexible data structure Copyright @ 2025 Oracle and/or its affiliates. 5
9. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... ◦ very quick initial development • Flexible data structure ◦ embedded arrays or objects Copyright @ 2025 Oracle and/or its affiliates. 5
10. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... ◦ very quick initial development • Flexible data structure ◦ embedded arrays or objects ◦ valid solution when natural data can't be modelized optimaly into a relational model Copyright @ 2025 Oracle and/or its affiliates. 5
11. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... ◦ very quick initial development • Flexible data structure ◦ embedded arrays or objects ◦ valid solution when natural data can't be modelized optimaly into a relational model ◦ objects persistance without the use of any ORM - mapping object-oriented Copyright @ 2025 Oracle and/or its affiliates. 5
12. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... ◦ very quick initial development • Flexible data structure ◦ embedded arrays or objects ◦ valid solution when natural data can't be modelized optimaly into a relational model ◦ objects persistance without the use of any ORM - mapping object-oriented • JSON Copyright @ 2025 Oracle and/or its affiliates. 5
13. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... ◦ very quick initial development • Flexible data structure ◦ embedded arrays or objects ◦ valid solution when natural data can't be modelized optimaly into a relational model ◦ objects persistance without the use of any ORM - mapping object-oriented • JSON ◦ close to frontend Copyright @ 2025 Oracle and/or its affiliates. 5
14. NoSQL Document Store • Schemaless ◦ no schema design, no normalization, no foreign keys, no data types, ... ◦ very quick initial development • Flexible data structure ◦ embedded arrays or objects ◦ valid solution when natural data can't be modelized optimaly into a relational model ◦ objects persistance without the use of any ORM - mapping object-oriented • JSON ◦ close to frontend ◦ easy to learn Copyright @ 2025 Oracle and/or its affiliates. 5
15. How DBAs see data Copyright @ 2025 Oracle and/or its affiliates. 6
16. How DBAs see data How Developers see data { { "GNP" "GNP" : : 249704 249704, , "Name" "Name" : : "Belgium" "Belgium", , "government" "government" : : { { "GovernmentForm" "GovernmentForm" : : "Constitutional Monarchy, Federation" "Constitutional Monarchy, Federation", , "HeadOfState" "HeadOfState" : : "Philippe I" "Philippe I" } }, , "_id" "_id" : : "BEL" "BEL", , "IndepYear" "IndepYear" : : 1830 1830, , "demographics" "demographics" : : { { "Population" "Population" : : 10239000 10239000, , "LifeExpectancy" "LifeExpectancy" : : 77.8000030517578 77.8000030517578 } }, , } } Copyright @ 2025 Oracle and/or its affiliates. 6
17. And they still need to do Analytics SQL SQL SQL Copyright @ 2025 Oracle and/or its affiliates. 7
18. ... mmm but...how ? ? SQL SQL SQL Copyright @ 2025 Oracle and/or its affiliates. 8
19. Migrating the data (ETL) SQL SQL SQL ETL: Extraction-Transformation-Load Copyright @ 2025 Oracle and/or its affiliates. 9
20. • Faster development time • Easier to modify the 'schema' • Simple CRUD API • Data is unstructured • Di�cult to run queries for reporting NoSQL • Data can be structured and organized • Easier to run queries for reporting • Slower development time • Schema changes can be di�cult Relational Data NoSQL vs Relational Data Copyright @ 2025 Oracle and/or its affiliates. 10
21. What if there was a way to provide both SQL and NoSQL on one platform that has proven stability on well know technology with a large Community and a diverse ecosystem ? Copyright @ 2025 Oracle and/or its affiliates. 11
22. RDBMS or NoSQL ? Copyright @ 2025 Oracle and/or its affiliates. 12
23. RDBMS or NoSQL ? Why not both ? Copyright @ 2025 Oracle and/or its affiliates. 12
24. The MySQL Document Store ! Where SQL is now optional ! Copyright @ 2025 Oracle and/or its affiliates. 13
25. Is SQL now optional ?! Copyright @ 2025 Oracle and/or its affiliates. 14
26. Using MySQL Document Store ! SQL SQL SQL Copyright @ 2025 Oracle and/or its affiliates. 15
27. The Solution MySQL Document Store Copyright @ 2025 Oracle and/or its affiliates. 16
28. Built on the MySQL JSON Data type and Proven MySQL Server Technology • Provides a schema �exible JSON Document Store • No SQL required • No need to de�ne all possible a�ributes, tables, etc. • Uses new X DevAPI • Can leverage generated column to extract JSON values into materialized columns that can be indexed for fast SQL searches. • Document can be ~1GB ◦ It's a column in a row of a table ◦ It cannot exceed max_allowed_packet • Allows use of modern programming styles ◦ No more embedded strings of SQL in your code ◦ Easy to read • Also works with relational Tables • Proven MySQL Technology • Compatible with all existing MySQL solutions Copyright @ 2025 Oracle and/or its affiliates. 17
29. • Table Columns* ◦ _id ▪ varbinary(32) ◦ doc ▪ JSON ◦ _json_schema ▪ JSON * others if you add indexes • Schema ◦ Schema - database • Collection ◦ Table • Document ◦ Row in table ◦ a Record Anatomy of MySQL Document Store Copyright @ 2025 Oracle and/or its affiliates. 18
30. X Protocol Connectors Copyright @ 2025 Oracle and/or its affiliates. 19
31. Installation & Setup MySQL Document Store Copyright @ 2025 Oracle and/or its affiliates. 20
32. Installing MySQL Document Store • install MySQL 8.x or 9.0 Copyright @ 2025 Oracle and/or its affiliates. 21
33. Installing MySQL Document Store • install MySQL 8.x or 9.0 • install MySQL Shell Copyright @ 2025 Oracle and/or its affiliates. 21
34. Installing MySQL Document Store • install MySQL 8.x or 9.0 • install MySQL Shell • install MySQL Connector for your programming language Copyright @ 2025 Oracle and/or its affiliates. 21
35. Installing MySQL Document Store • install MySQL 8.x or 9.0 • install MySQL Shell • install MySQL Connector for your programming language ◦ Connector/J for Java ◦ php-pecl-mysql-xdevapi for PHP ◦ mysql-connector-python for Python, ... Copyright @ 2025 Oracle and/or its affiliates. 21
36. Installing MySQL Document Store • install MySQL 8.x or 9.0 • install MySQL Shell • install MySQL Connector for your programming language ◦ Connector/J for Java ◦ php-pecl-mysql-xdevapi for PHP ◦ mysql-connector-python for Python, ... Simply ensure that your �rewall se�ings permit connections via port 33060 (X Protocol); no additional installations or plugins are required. Copyright @ 2025 Oracle and/or its affiliates. 21
37. MySQL HeatWave Database Service MySQL HeatWave Database Service on OCI is the only MySQL DBaaS including X Protocol. Copyright @ 2025 Oracle and/or its affiliates. 22
38. Migration from MongoDB to MySQL DS For this example, I am using the well known restaurants collection: $ mongoexport $ mongoexport -c -c restaurants restaurants > > restaurants.json restaurants.json connected to: connected to: 127.0 127.0.0.1 .0.1 exported exported 25359 25359 records records Copyright @ 2025 Oracle and/or its affiliates. 23
39. Migration from MongoDB to MySQL DS For this example, I am using the well known restaurants collection: $ mongoexport $ mongoexport -c -c restaurants restaurants > > restaurants.json restaurants.json connected to: connected to: 127.0 127.0.0.1 .0.1 exported exported 25359 25359 records records JS JS> > util util. .importJson importJson( ('restaurants.json' 'restaurants.json', ,{ {convertBsonOid convertBsonOid: : true true} }) ) Importing from file Importing from file "restaurants.json" "restaurants.json" to collection to collection ` `docstore docstore` `. .` `restaurants restaurants` ` in in MySQL Server at localhost MySQL Server at localhost: :33060 33060 . .. . 25359. 25359.. . 25359 25359 Processed Processed 15.60 15.60 MB MB in in 25359 25359 documents documents in in 0.9976 0.9976 sec sec ( (25 25. .36K documents 36K documents/ /s s) ) Total successfully imported documents Total successfully imported documents 25359 25359 ( (25 25. .36K documents 36K documents/ /s s) ) Copyright @ 2025 Oracle and/or its affiliates. 23
40. Copyright @ 2025 Oracle and/or its affiliates. 24
41. Copyright @ 2025 Oracle and/or its affiliates. 25
42. Copyright @ 2025 Oracle and/or its affiliates. 26
43. Copyright @ 2025 Oracle and/or its affiliates. 27
44. Let's make a query JS > restaurants.find() That's too much records to show in here... let's limit it Copyright @ 2025 Oracle and/or its affiliates. 28
45. Copyright @ 2025 Oracle and/or its affiliates. 29
46. Another example Copyright @ 2025 Oracle and/or its affiliates. 30
47. With a selection �lter: Copyright @ 2025 Oracle and/or its affiliates. 31
48. With a selection �lter (2): Copyright @ 2025 Oracle and/or its affiliates. 32
49. Syntax slightly di�erent than MongoDB Copyright @ 2025 Oracle and/or its affiliates. 33
50. Syntax slightly di�erent than MongoDB Copyright @ 2025 Oracle and/or its affiliates. 33
51. And for developers ? Copyright @ 2025 Oracle and/or its affiliates. 34
52. And for developers ? $session $session = = mysql_xdevapi mysql_xdevapi\ \getSession getSession( ("mysqlx://fred:MyP@ssw0rd%@localhost" "mysqlx://fred:MyP@ssw0rd%@localhost") ); ; $schema $schema = = $session $session-> ->getSchema getSchema( ("docstore" "docstore") ); ; $collection $collection = = $schema $schema-> ->getCollection getCollection( ("restaurants" "restaurants") ); ; $results $results = = $collection $collection-> ->find find( ($search $search) )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; ... ... foreach foreach ( ($results $results as as $doc $doc) ) { { echo echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>" "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; ; echo echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>" "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; ; } } Copyright @ 2025 Oracle and/or its affiliates. 35
53. And for developers ? $session $session = = mysql_xdevapi mysql_xdevapi\ \getSession getSession( ("mysqlx://fred:MyP@ssw0rd%@localhost" "mysqlx://fred:MyP@ssw0rd%@localhost") ); ; $schema $schema = = $session $session-> ->getSchema getSchema( ("docstore" "docstore") ); ; $collection $collection = = $schema $schema-> ->getCollection getCollection( ("restaurants" "restaurants") ); ; $results $results = = $collection $collection-> ->find find( ($search $search) )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; ... ... foreach foreach ( ($results $results as as $doc $doc) ) { { echo echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>" "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; ; echo echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>" "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; ; } } Easy, using only CRUD operations ! Copyright @ 2025 Oracle and/or its affiliates. 35
54. And for developers ? $session $session = = mysql_xdevapi mysql_xdevapi\ \getSession getSession( ("mysqlx://fred:MyP@ssw0rd%@localhost" "mysqlx://fred:MyP@ssw0rd%@localhost") ); ; $schema $schema = = $session $session-> ->getSchema getSchema( ("docstore" "docstore") ); ; $collection $collection = = $schema $schema-> ->getCollection getCollection( ("restaurants" "restaurants") ); ; $results $results = = $collection $collection-> ->find find( ($search $search) )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; ... ... foreach foreach ( ($results $results as as $doc $doc) ) { { echo echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>" "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; ; echo echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>" "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; ; } } Easy, using only CRUD operations ! Not a single SQL statement ! Copyright @ 2025 Oracle and/or its affiliates. 35
55. With import import * *; ; class class Main Main { { public public static static void void main main( (String String args args[ [] ]) ) { { Session Session mySession mySession = = new new SessionFactory SessionFactory( () ) . .getSession getSession( ("mysqlx://localhost:33060/docstore?user=resto&password=Passw0rd!" "mysqlx://localhost:33060/docstore?user=resto&password=Passw0rd!") ); ; Schema Schema myDb myDb = = mySession mySession. .getSchema getSchema( ("docstore" "docstore") ); ; Collection Collection myColl myColl = = myDb myDb. .getCollection getCollection( ("restaurants" "restaurants") ); ; DocResult DocResult myDocs myDocs = = myColl myColl. .find find( ("name like :param" "name like :param") ). .limit limit( (1 1) ) . .bind bind( ("param" "param", , "Green%" "Green%") ). .execute execute( () ); ; System System. .out out. .println println( (myDocs myDocs. .fetchOne fetchOne( () )) ); ; mySession mySession. .close close( () ); ; } } } } Copyright @ 2025 Oracle and/or its affiliates. com com. .mysql mysql. .cj cj. .xdevapi xdevapi. . 36
56. Li�le Demo $ javac $ javac -classpath -classpath "lib/*" "lib/*" -d -d bin src/Resto.java bin src/Resto.java $ $ java java -classpath -classpath "bin:lib/*" "bin:lib/*" Resto Resto Connecting to database Connecting to database.. ... . Enter a string to search Enter a string to search in in the database: fred the database: fred Searching Searching for for 'fred' 'fred' in in the database the database.. ... . Name: Name: | | Borough: Borough: Cafe Cafe 212 212/Columbia Catering Kitchen - Alfred Lerner Hall /Columbia Catering Kitchen - Alfred Lerner Hall | | Manhattan Manhattan Ferris Booth Commons - Alfred Lerner Hall Ferris Booth Commons - Alfred Lerner Hall | | Manhattan Manhattan Cafe East - Alfred Lerner Hall Cafe East - Alfred Lerner Hall | | Manhattan Manhattan Alfredo Alfredo 100 100 | | Manhattan Manhattan Copyright @ 2025 Oracle and/or its affiliates. 37
57. CRUD operations The computer acronym CRUD (for Create, Read, Update, Delete) designates the four basic operations for data persistence, in particular the storage of information in a database. Copyright @ 2025 Oracle and/or its affiliates. 38
58. CRUD operations on collections Add a document collection collection. .add add( ({ { name name: : 'fred' 'fred', , age age: : 46 46 } }) ) . .add add( ({ { name name: : 'scott' 'scott', , age age: : 47 47 } }) ) . .execute execute( () ) collection collection. .add add( ([ [ { { name name: : 'dimo' 'dimo', , age age: : 50 50 } }, , { { name name: : 'kenny' 'kenny', , age age: : 25 25 } } ] ]) ). .execute execute( () ) Copyright @ 2025 Oracle and/or its affiliates. 39
59. collection collection. .modify modify( ('name = :name' 'name = :name') ) . .bind bind( ('name' 'name', , 'fred' 'fred') ) . .set set( ('age' 'age', , 43 43) ) . .sort sort( ('name ASC' 'name ASC') ) . .limit limit( (1 1) ) . .execute execute( () ) collection collection. .modify modify( ('name = :name' 'name = :name') ) . .bind bind( ('name' 'name', , 'fred' 'fred') ) . .patch patch( ({ { age age: : 43 43, , active active: : false false } }) ) . .sort sort( ('name DESC' 'name DESC') ) . .limit limit( (1 1) ) . .execute execute( () ) CRUD operations on collections Modify a document Copyright @ 2025 Oracle and/or its affiliates. 40
60. CRUD operations on collections Delete of a document collection collection. .remove remove( ('name = :name' 'name = :name') ) . .bind bind( ('name' 'name', , 'fred' 'fred') ) . .sort sort( ('age ASC' 'age ASC') ) . .limit limit( (1 1) ) . .execute execute( () ) Copyright @ 2025 Oracle and/or its affiliates. 41
61. MySQL Document Store Objects Summary Copyright @ 2025 Oracle and/or its affiliates. 42
62. All you need to know about the X Dev API is here: h�ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2025 Oracle and/or its affiliates. 43
63. MySQL Document Store is full ACID compliant we do care about your data Copyright @ 2025 Oracle and/or its affiliates. 44
64. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: Copyright @ 2025 Oracle and/or its affiliates. 45
65. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: • innodb_�ush_log_at_trx_commit = 1 Copyright @ 2025 Oracle and/or its affiliates. 45
66. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: • innodb_�ush_log_at_trx_commit = 1 • innodb_doublewrite = ON Copyright @ 2025 Oracle and/or its affiliates. 45
67. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: • innodb_�ush_log_at_trx_commit = 1 • innodb_doublewrite = ON • sync_binlog = 1 Copyright @ 2025 Oracle and/or its affiliates. 45
68. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: • innodb_�ush_log_at_trx_commit = 1 • innodb_doublewrite = ON • sync_binlog = 1 • transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... Copyright @ 2025 Oracle and/or its affiliates. 45
69. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: • innodb_�ush_log_at_trx_commit = 1 • innodb_doublewrite = ON • sync_binlog = 1 • transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... Durability is important. Copyright @ 2025 Oracle and/or its affiliates. 45
70. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: • innodb_�ush_log_at_trx_commit = 1 • innodb_doublewrite = ON • sync_binlog = 1 • transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... Durability is important. We do care about your data ! Copyright @ 2025 Oracle and/or its affiliates. 45
71. ACID - Support of Transactions Copyright @ 2025 Oracle and/or its affiliates. 46
72. ACID - Support of Transactions Copyright @ 2025 Oracle and/or its affiliates. 47
73. OK we have Doc Store, CRUD & ACID but what makes MySQL Document Store unique? Copyright @ 2025 Oracle and/or its affiliates. 48
74. Challenge: list the best restaurant of each type of food and show the top 10, with the best one �rst ! don't forget that all these restaurants are just JSON documents Copyright @ 2025 Oracle and/or its affiliates. 49
75. Copyright @ 2025 Oracle and/or its affiliates. 50
76. NoSQL as SQL - aggregation Copyright @ 2025 Oracle and/or its affiliates. 51
77. NoSQL as SQL - aggregation Copyright @ 2025 Oracle and/or its affiliates. 52
78. NoSQL as SQL - aggregation Copyright @ 2025 Oracle and/or its affiliates. 53
79. NoSQL as SQL - aggregation Copyright @ 2025 Oracle and/or its affiliates. 54
80. NoSQL or SQL You have the possibility to write clean and neat code: Copyright @ 2025 Oracle and/or its affiliates. 55
81. NoSQL or SQL You have the possibility to write clean and neat code: $results $results = = $collection $collection-> ->find find( ('cuisine like "italian"' 'cuisine like "italian"') )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; Copyright @ 2025 Oracle and/or its affiliates. 55
82. NoSQL or SQL You have the possibility to write clean and neat code: $results $results = = $collection $collection-> ->find find( ('cuisine like "italian"' 'cuisine like "italian"') )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; And use SQL only when it's necessary: Copyright @ 2025 Oracle and/or its affiliates. 55
83. NoSQL or SQL You have the possibility to write clean and neat code: $results $results = = $collection $collection-> ->find find( ('cuisine like "italian"' 'cuisine like "italian"') )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; And use SQL only when it's necessary: $results $results = = $session $session-> ->sql sql( (' select * from (with cte1 as (select doc->>"$.name" as name, ' select * from (with cte1 as (select doc->>"$.name" as name, doc->>"$.cuisine" as cuisine, ( select avg(score) from json_table(doc, "$.grades[*]" doc->>"$.cuisine" as cuisine, ( select avg(score) from json_table(doc, "$.grades[*]" columns (score int path "$.score")) as r) as avg_score from restaurants) columns (score int path "$.score")) as r) as avg_score from restaurants) select *, row_number() over ( partition by cuisine order by avg_score desc) as `rank` select *, row_number() over ( partition by cuisine order by avg_score desc) as `rank` from cte1 order by `rank`, avg_score desc) b where `rank`=1' from cte1 order by `rank`, avg_score desc) b where `rank`=1') )-> ->execute execute( () ); ; Copyright @ 2025 Oracle and/or its affiliates. 55
84. All in the same MySQL X Session ! Copyright @ 2025 Oracle and/or its affiliates. 56
85. You can mix NoSQL & SQL as you want: Copyright @ 2025 Oracle and/or its affiliates. 57
86. Best of Both Worlds: JSON_TABLE What are the maximum 10 ratings ever given to a restaurant? Copyright @ 2025 Oracle and/or its affiliates. 58
87. Best of Both Worlds: JSON_TABLE What are the maximum 10 ratings ever given to a restaurant? Cool... but my app only processes JSON ! Copyright @ 2025 Oracle and/or its affiliates. 58
88. Best of Both Worlds: JSON_TABLE (2) With JSON output: Copyright @ 2025 Oracle and/or its affiliates. 59
89. Best of Both Worlds: JSON_TABLE (3) Remember the challenge ? Copyright @ 2025 Oracle and/or its affiliates. 60
90. Best of Both Worlds: JSON_TABLE (3) Remember the challenge ? Copyright @ 2025 Oracle and/or its affiliates. 60
91. Best of Both Worlds: JOINS Now, you know the di�erence between a collection and a table: Copyright @ 2025 Oracle and/or its affiliates. 61
92. Best of Both Worlds: JOINS Now, you know the di�erence between a collection and a table: Copyright @ 2025 Oracle and/or its affiliates. 61
93. Best of Both Worlds: JOINS (2) And of course with MySQL Document Store we can also JOIN them together! Copyright @ 2025 Oracle and/or its affiliates. 62
94. Best of Both Worlds: JOINS (2) And of course with MySQL Document Store we can also JOIN them together! Copyright @ 2025 Oracle and/or its affiliates. 62
95. Never satisfied, always wanting more! Copyright @ 2025 Oracle and/or its affiliates. 63
96. Never satis�ed, always wanting more! Copyright @ 2025 Oracle and/or its affiliates. 64
97. From total mess (schemaless) Copyright @ 2025 Oracle and/or its affiliates. 65
98. From total mess (schemaless) To organized "mess" Copyright @ 2025 Oracle and/or its affiliates. 65
99. Cleaning the mess with some rules Best of both worlds: mixing constraints and JSON validation: Copyright @ 2025 Oracle and/or its affiliates. 66
100. Cleaning the mess with some rules Best of both worlds: mixing constraints and JSON validation: Result in action: Copyright @ 2025 Oracle and/or its affiliates. 66
101. JSON Validation: best practice It's nicer to validate the JSON at the collection level: Copyright @ 2025 Oracle and/or its affiliates. 67
102. JSON Validation: best practice Copyright @ 2025 Oracle and/or its affiliates. 68
103. JSON Validation: best practice And now we see an usage's illustration of _json_schema: Copyright @ 2025 Oracle and/or its affiliates. 68
104. Another Example with validation Copyright @ 2025 Oracle and/or its affiliates. 69
105. Another Example with validation (2) Copyright @ 2025 Oracle and/or its affiliates. 70
106. Another Example with validation (2) Copyright @ 2025 Oracle and/or its affiliates. 71
107. Acceleration with MySQL HeatWave MySQL HeatWave Database Service supports the Dev X API allowing the X Protocol, but if you want to also bene�t from HeatWave Cluster, query accelerator, you need to make a change to your collection: MySQL MySQL > > ALTER ALTER TABLE TABLE restaurants restaurants MODIFY MODIFY _id _id CHAR CHAR( (28 28) ) CHARSET CHARSET latin1 latin1 GENERATED ALWAYS GENERATED ALWAYS AS AS ( (JSON_UNQUOTE JSON_UNQUOTE( (JSON_EXTRACT JSON_EXTRACT( (` `doc doc` `, ,_utf8mb4 _utf8mb4'$._id' '$._id') )) )) ) STORED STORED NOT NOT NULL NULL; ; Copyright @ 2025 Oracle and/or its affiliates. 72
108. Acceleration with MySQL HeatWave (2) Let's have a look at our collection: JS JS > > db db. .restaurants restaurants. .count count( () ) 2821923 2821923 Copyright @ 2025 Oracle and/or its affiliates. 73
109. Acceleration with MySQL HeatWave (2) Let's have a look at our collection: JS JS > > db db. .restaurants restaurants. .count count( () ) 2821923 2821923 We have 2,821,923 documents ! Copyright @ 2025 Oracle and/or its affiliates. 73
110. SQL SQL > > select select doc doc- ->> >>"$.borough" "$.borough" borough borough, , count count( (* *) ) tot tot, , max max( (length length( (doc doc- ->> >>"$.name" "$.name") )) ) longest_name longest_name, , min min( (length length( (doc doc- ->> >>"$.name" "$.name") )) ) shortest_name shortest_name, , round round( (avg avg( (length length( (doc doc- ->> >>"$.name" "$.name") )) ), ,2 2) ) avg_name_length avg_name_length from from restaurants restaurants where where doc doc- ->> >>"$.cuisine" "$.cuisine" collate collate utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci like like 'belgian' 'belgian' group group by by borough borough order order by by tot tot desc desc limit limit 10 10; ; . .. .. .. . 10 10 rows rows in in set set ( (1.1713 1.1713 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 74
111. Acceleration with MySQL HeatWave (3) We can now load the collection to HeatWave Cluster: MySQL MySQL > > CALL CALL sys sys. .heatwave_load heatwave_load( (JSON_ARRAY JSON_ARRAY( ('docstore' 'docstore') ), , NULL NULL) ); ; . .. .. . + +-------------------------------------------------------------------------------+ -------------------------------------------------------------------------------+ | | LOAD LOAD SUMMARY SUMMARY | | + +-------------------------------------------------------------------------------+ -------------------------------------------------------------------------------+ | | | | | | SCHEMA SCHEMA TABLES TABLES TABLES TABLES COLUMNS COLUMNS LOAD LOAD | | | | NAME LOADED FAILED LOADED DURATION NAME LOADED FAILED LOADED DURATION | | | | ------ ------ ------ ------- -------- | ------ ------ ------ ------- -------- | | | ` `docstore docstore` ` 1 1 0 0 2 2 11.41 11.41 s s | | | | | | + +-------------------------------------------------------------------------------+ -------------------------------------------------------------------------------+ Copyright @ 2025 Oracle and/or its affiliates. 75
112. Acceleration with MySQL HeatWave (4) For example with 2,162,520 documents we go from 1.1713 seconds to 0.1922 seconds when using HeatWeave Cluster. . .. .. .. . 10 10 rows rows in in set set ( (0.1922 0.1922 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 76
113. MySQL Document Store is compatible with all MySQL Architectures Copyright @ 2025 Oracle and/or its affiliates. 77
114. Ready for more ? MySQL Document Store on steroïd, the future is called: MRS Copyright @ 2025 Oracle and/or its affiliates. 78
115. MySQL REST Service (MRS) Evolution of DB Development Architectures Copyright @ 2025 Oracle and/or its affiliates. 79
116. MySQL REST Service (MRS) Evolution of DB Development Architectures Copyright @ 2025 Oracle and/or its affiliates. 80
117. MySQL REST Service (MRS) Evolution of DB Development Architectures Copyright @ 2025 Oracle and/or its affiliates. 81
118. MySQL REST Service (MRS) Evolution of DB Development Architectures Copyright @ 2025 Oracle and/or its affiliates. 82
119. MySQL REST Service (MRS) Evolution of DB Development Architectures Copyright @ 2025 Oracle and/or its affiliates. 83
120. MySQL REST Service (MRS) Evolution of DB Development Architectures Copyright @ 2025 Oracle and/or its affiliates. 84
121. • MRS is built on the concept of ORDS, but targeting the strengths of MySQL ◦ focus on MySQL performance ◦ focus on MySQL scalability ◦ MySQL/HeatWave metadata storage - no dependencies on an Oracle DB instance • ORDS & APEX compatible • Developer oriented ◦ low learning curve ◦ easy to learn and use ◦ beginner friendly ◦ manageable via GUI or code ... Copyright © 2023, Oracle and/or its affi liates MySQL Router MySQL Router MySQL Router MySQL Router … MySQL Shell OAuth2 Service MySQL/HeatWave Client App Client App Client App … Client App Client App Client App … Client App Client App Client App … Client App Client App Client App MRS Plugin Auth HTTPS Server SQL Load Balancer Load Balancer MySQL REST Service (MRS) - Architecture ... MRS Metadata Schema Application Data MySQLShell for VS Code MySQL REST Service (MRS) Fast, Secure HTTPS Access for MySQL Data Copyright @ 2025 Oracle and/or its affiliates. 85
122. MySQL REST Service (MRS) - Architecture ... MySQL Router MySQL Router MySQL Router MySQL Router … MySQL Shell OAuth2 Service MySQL/HeatWave Client App Client App Client App … Client App Client App Client App … Client App Client App Client App … Client App Client App Client App MRS Plugin Auth HTTPS Server SQL Load Balancer Load Balancer ... MRS Metadata Schema Application Data MySQL Shell for VS Code Copyright @ 2025 Oracle and/or its affiliates. 86
123. RESTful Web Services • auto REST for tables, view, procedures and functions • {JSON} responses with paged results • developer support (GUI, CLI, API) • support for popular OAuth2 services Powerful REST Data Mapping • full SQL support for joins and nested JSON REST endpoints • Visual Data Mapping Editor ◦ build complex JSON structures with a �ew clicks • SQL & SDK interface preview MySQL REST Service (MRS) Features Overview Copyright @ 2025 Oracle and/or its affiliates. 87
124. Full SQL Support & SDK API • fully manageable through SQL • tailored SDK for all RESTful Endpoints • popular, Prisma-like API, like prototyping TypeScript SDK API whith live prototyping of REST queries Fully manageable through SQL MySQL REST Service (MRS) Features Overview (2) Copyright @ 2025 Oracle and/or its affiliates. 88
125. MySQL REST Service (MRS) Visual REST Mapping View Editor Intuitive WYSIWYG Editor to design REST Mappings • creation of complex mappings with a few clicks • automatic database schema analysis • SQL Preview Copyright @ 2025 Oracle and/or its affiliates. 89
126. MySQL REST Service (MRS) The MySQL REST SERVICE (MRS) adds full support for JSON/Relational Duality by enabling fast and secure HTTPS access for your MySQL data. Copyright @ 2025 Oracle and/or its affiliates. 90
127. MySQL REST Service (MRS) (2) Copyright @ 2025 Oracle and/or its affiliates. 91
128. MySQL REST Service (MRS) (3) Do you remember the JOIN between our collection and two tables ? Copyright @ 2025 Oracle and/or its affiliates. 92
129. MySQL REST Service (MRS) (3) Do you remember the JOIN between our collection and two tables ? Let's create a VIEW: Copyright @ 2025 Oracle and/or its affiliates. 92
130. MySQL REST Service (MRS) (4) Copyright @ 2025 Oracle and/or its affiliates. 93
131. MySQL REST Service (MRS) (5) Copyright @ 2025 Oracle and/or its affiliates. 94
132. Conclusion what do I gain ? Copyright @ 2025 Oracle and/or its affiliates. 95
133. RDMBS • Data integrity • ACID Compliant • Transactions • SQL Conclusion This is the best of the two worlds in one product ! Copyright @ 2025 Oracle and/or its affiliates. 96
134. RDMBS • Data integrity • ACID Compliant • Transactions • SQL NoSQL • schemaless • �exible data structure • easy to start (CRUD) Conclusion This is the best of the two worlds in one product ! Copyright @ 2025 Oracle and/or its affiliates. 96
135. Conclusion (2) And with MRS, MySQL serves data to client applications via a HTTPS REST interface. Copyright @ 2025 Oracle and/or its affiliates. 97
136. NoSQL + SQL = MySQL Copyright @ 2025 Oracle and/or its affiliates. 98
137. NoSQL + SQL = MySQL Copyright @ 2025 Oracle and/or its affiliates. 98
138. Share your to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2025 Oracle and/or its affiliates. 99
139. Questions ? Copyright @ 2025 Oracle and/or its affiliates. 100