Slide 1
Slide 1 text
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
Slide 2
Slide 2 text
Who am I ?
about.me/lefred
Copyright @ 2025 Oracle and/or its affiliates.
2
Slide 3
Slide 3 text
• @lefred
• @lefredbe.bsky.social
• @[email protected]
• 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
Slide 4
Slide 4 text
MySQL 8.0 / 9.0 Document Store
discovery of a new world
Copyright @ 2025 Oracle and/or its affiliates.
4
Slide 5
Slide 5 text
NoSQL Document Store
• Schemaless
Copyright @ 2025 Oracle and/or its affiliates.
5
Slide 6
Slide 6 text
NoSQL Document Store
• Schemaless
◦ no schema design, no normalization, no foreign keys, no data types, ...
Copyright @ 2025 Oracle and/or its affiliates.
5
Slide 7
Slide 7 text
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
Slide 8
Slide 8 text
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
Slide 9
Slide 9 text
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
Slide 10
Slide 10 text
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
Slide 11
Slide 11 text
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
Slide 12
Slide 12 text
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
Slide 13
Slide 13 text
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
Slide 14
Slide 14 text
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
Slide 15
Slide 15 text
How DBAs see data
Copyright @ 2025 Oracle and/or its affiliates.
6
Slide 16
Slide 16 text
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
Slide 17
Slide 17 text
And they still need to do Analytics
SQL
SQL
SQL
Copyright @ 2025 Oracle and/or its affiliates.
7
Slide 18
Slide 18 text
... mmm but...how ?
?
SQL
SQL
SQL
Copyright @ 2025 Oracle and/or its affiliates.
8
Slide 19
Slide 19 text
Migrating the data (ETL)
SQL
SQL
SQL
ETL: Extraction-Transformation-Load
Copyright @ 2025 Oracle and/or its affiliates.
9
Slide 20
Slide 20 text
• 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
Slide 21
Slide 21 text
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
Slide 22
Slide 22 text
RDBMS or NoSQL ?
Copyright @ 2025 Oracle and/or its affiliates.
12
Slide 23
Slide 23 text
RDBMS or NoSQL ?
Why not both ?
Copyright @ 2025 Oracle and/or its affiliates.
12
Slide 24
Slide 24 text
The MySQL Document Store !
Where SQL is now optional !
Copyright @ 2025 Oracle and/or its affiliates.
13
Slide 25
Slide 25 text
Is SQL now optional ?!
Copyright @ 2025 Oracle and/or its affiliates.
14
Slide 26
Slide 26 text
Using MySQL Document Store !
SQL
SQL
SQL
Copyright @ 2025 Oracle and/or its affiliates.
15
Slide 27
Slide 27 text
The Solution
MySQL Document Store
Copyright @ 2025 Oracle and/or its affiliates.
16
Slide 28
Slide 28 text
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
Slide 29
Slide 29 text
•
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
Slide 30
Slide 30 text
X Protocol Connectors
Copyright @ 2025 Oracle and/or its affiliates.
19
Slide 31
Slide 31 text
Installation & Setup
MySQL Document Store
Copyright @ 2025 Oracle and/or its affiliates.
20
Slide 32
Slide 32 text
Installing MySQL Document Store
• install MySQL 8.x or 9.0
Copyright @ 2025 Oracle and/or its affiliates.
21
Slide 33
Slide 33 text
Installing MySQL Document Store
• install MySQL 8.x or 9.0
• install MySQL Shell
Copyright @ 2025 Oracle and/or its affiliates.
21
Slide 34
Slide 34 text
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
Slide 35
Slide 35 text
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
Slide 36
Slide 36 text
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
Slide 37
Slide 37 text
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
Slide 38
Slide 38 text
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
Slide 39
Slide 39 text
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
Slide 40
Slide 40 text
Copyright @ 2025 Oracle and/or its affiliates.
24
Slide 41
Slide 41 text
Copyright @ 2025 Oracle and/or its affiliates.
25
Slide 42
Slide 42 text
Copyright @ 2025 Oracle and/or its affiliates.
26
Slide 43
Slide 43 text
Copyright @ 2025 Oracle and/or its affiliates.
27
Slide 44
Slide 44 text
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
Slide 45
Slide 45 text
Copyright @ 2025 Oracle and/or its affiliates.
29
Slide 46
Slide 46 text
Another example
Copyright @ 2025 Oracle and/or its affiliates.
30
Slide 47
Slide 47 text
With a selection �lter:
Copyright @ 2025 Oracle and/or its affiliates.
31
Slide 48
Slide 48 text
With a selection �lter (2):
Copyright @ 2025 Oracle and/or its affiliates.
32
Slide 49
Slide 49 text
Syntax slightly di�erent than MongoDB
Copyright @ 2025 Oracle and/or its affiliates.
33
Slide 50
Slide 50 text
Syntax slightly di�erent than MongoDB
Copyright @ 2025 Oracle and/or its affiliates.
33
Slide 51
Slide 51 text
And for developers ?
Copyright @ 2025 Oracle and/or its affiliates.
34
Slide 52
Slide 52 text
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 "${doc[name]}"
"${doc[name]}";
;
echo
echo "${doc[borough]}${doc[cuisine]}"
"${doc[borough]}${doc[cuisine]}";
;
}
}
Copyright @ 2025 Oracle and/or its affiliates.
35
Slide 53
Slide 53 text
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 "${doc[name]}"
"${doc[name]}";
;
echo
echo "${doc[borough]}${doc[cuisine]}"
"${doc[borough]}${doc[cuisine]}";
;
}
}
Easy, using only CRUD operations !
Copyright @ 2025 Oracle and/or its affiliates.
35
Slide 54
Slide 54 text
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 "${doc[name]}"
"${doc[name]}";
;
echo
echo "${doc[borough]}${doc[cuisine]}"
"${doc[borough]}${doc[cuisine]}";
;
}
}
Easy, using only CRUD operations !
Not a single SQL statement !
Copyright @ 2025 Oracle and/or its affiliates.
35
Slide 55
Slide 55 text
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
Slide 56
Slide 56 text
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
Slide 57
Slide 57 text
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
Slide 58
Slide 58 text
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
Slide 59
Slide 59 text
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
Slide 60
Slide 60 text
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
Slide 61
Slide 61 text
MySQL Document Store Objects Summary
Copyright @ 2025 Oracle and/or its affiliates.
42
Slide 62
Slide 62 text
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
Slide 63
Slide 63 text
MySQL Document Store
is full ACID compliant
we do care about your data
Copyright @ 2025 Oracle and/or its affiliates.
44
Slide 64
Slide 64 text
Document Store Full ACID !
The solution relies on the proven MySQL InnoDB strength & robustness:
Copyright @ 2025 Oracle and/or its affiliates.
45
Slide 65
Slide 65 text
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
Slide 66
Slide 66 text
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
Slide 67
Slide 67 text
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
Slide 68
Slide 68 text
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
Slide 69
Slide 69 text
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
Slide 70
Slide 70 text
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
Slide 71
Slide 71 text
ACID - Support of Transactions
Copyright @ 2025 Oracle and/or its affiliates.
46
Slide 72
Slide 72 text
ACID - Support of Transactions
Copyright @ 2025 Oracle and/or its affiliates.
47
Slide 73
Slide 73 text
OK we have Doc Store, CRUD & ACID
but what makes MySQL Document Store unique?
Copyright @ 2025 Oracle and/or its affiliates.
48
Slide 74
Slide 74 text
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
Slide 75
Slide 75 text
Copyright @ 2025 Oracle and/or its affiliates.
50
Slide 76
Slide 76 text
NoSQL as SQL - aggregation
Copyright @ 2025 Oracle and/or its affiliates.
51
Slide 77
Slide 77 text
NoSQL as SQL - aggregation
Copyright @ 2025 Oracle and/or its affiliates.
52
Slide 78
Slide 78 text
NoSQL as SQL - aggregation
Copyright @ 2025 Oracle and/or its affiliates.
53
Slide 79
Slide 79 text
NoSQL as SQL - aggregation
Copyright @ 2025 Oracle and/or its affiliates.
54
Slide 80
Slide 80 text
NoSQL or SQL
You have the possibility to write clean and neat code:
Copyright @ 2025 Oracle and/or its affiliates.
55
Slide 81
Slide 81 text
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
Slide 82
Slide 82 text
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
Slide 83
Slide 83 text
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
Slide 84
Slide 84 text
All in the same MySQL X Session !
Copyright @ 2025 Oracle and/or its affiliates.
56
Slide 85
Slide 85 text
You can mix NoSQL & SQL as you want:
Copyright @ 2025 Oracle and/or its affiliates.
57
Slide 86
Slide 86 text
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
Slide 87
Slide 87 text
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
Slide 88
Slide 88 text
Best of Both Worlds: JSON_TABLE (2)
With JSON output:
Copyright @ 2025 Oracle and/or its affiliates.
59
Slide 89
Slide 89 text
Best of Both Worlds: JSON_TABLE (3)
Remember the challenge ?
Copyright @ 2025 Oracle and/or its affiliates.
60
Slide 90
Slide 90 text
Best of Both Worlds: JSON_TABLE (3)
Remember the challenge ?
Copyright @ 2025 Oracle and/or its affiliates.
60
Slide 91
Slide 91 text
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
Slide 92
Slide 92 text
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
Slide 93
Slide 93 text
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
Slide 94
Slide 94 text
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
Slide 95
Slide 95 text
Never satisfied, always wanting more!
Copyright @ 2025 Oracle and/or its affiliates.
63
Slide 96
Slide 96 text
Never satis�ed, always wanting more!
Copyright @ 2025 Oracle and/or its affiliates.
64
Slide 97
Slide 97 text
From total mess
(schemaless)
Copyright @ 2025 Oracle and/or its affiliates.
65
Slide 98
Slide 98 text
From total mess
(schemaless)
To organized
"mess"
Copyright @ 2025 Oracle and/or its affiliates.
65
Slide 99
Slide 99 text
Cleaning the mess with some rules
Best of both worlds: mixing constraints and JSON validation:
Copyright @ 2025 Oracle and/or its affiliates.
66
Slide 100
Slide 100 text
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
Slide 101
Slide 101 text
JSON Validation: best practice
It's nicer to validate the JSON at the collection level:
Copyright @ 2025 Oracle and/or its affiliates.
67
Slide 102
Slide 102 text
JSON Validation: best practice
Copyright @ 2025 Oracle and/or its affiliates.
68
Slide 103
Slide 103 text
JSON Validation: best practice
And now we see an usage's illustration of _json_schema:
Copyright @ 2025 Oracle and/or its affiliates.
68
Slide 104
Slide 104 text
Another Example with validation
Copyright @ 2025 Oracle and/or its affiliates.
69
Slide 105
Slide 105 text
Another Example with validation (2)
Copyright @ 2025 Oracle and/or its affiliates.
70
Slide 106
Slide 106 text
Another Example with validation (2)
Copyright @ 2025 Oracle and/or its affiliates.
71
Slide 107
Slide 107 text
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
Slide 108
Slide 108 text
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
Slide 109
Slide 109 text
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
Slide 110
Slide 110 text
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
Slide 111
Slide 111 text
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
Slide 112
Slide 112 text
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
Slide 113
Slide 113 text
MySQL Document Store is compatible with all
MySQL Architectures
Copyright @ 2025 Oracle and/or its affiliates.
77
Slide 114
Slide 114 text
Ready for more ?
MySQL Document Store on steroïd, the future is called:
MRS
Copyright @ 2025 Oracle and/or its affiliates.
78
Slide 115
Slide 115 text
MySQL REST Service (MRS)
Evolution of DB Development Architectures
Copyright @ 2025 Oracle and/or its affiliates.
79
Slide 116
Slide 116 text
MySQL REST Service (MRS)
Evolution of DB Development Architectures
Copyright @ 2025 Oracle and/or its affiliates.
80
Slide 117
Slide 117 text
MySQL REST Service (MRS)
Evolution of DB Development Architectures
Copyright @ 2025 Oracle and/or its affiliates.
81
Slide 118
Slide 118 text
MySQL REST Service (MRS)
Evolution of DB Development Architectures
Copyright @ 2025 Oracle and/or its affiliates.
82
Slide 119
Slide 119 text
MySQL REST Service (MRS)
Evolution of DB Development Architectures
Copyright @ 2025 Oracle and/or its affiliates.
83
Slide 120
Slide 120 text
MySQL REST Service (MRS)
Evolution of DB Development Architectures
Copyright @ 2025 Oracle and/or its affiliates.
84
Slide 121
Slide 121 text
• 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
Slide 122
Slide 122 text
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
Slide 123
Slide 123 text
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
Slide 124
Slide 124 text
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
Slide 125
Slide 125 text
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
Slide 126
Slide 126 text
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
Slide 127
Slide 127 text
MySQL REST Service (MRS) (2)
Copyright @ 2025 Oracle and/or its affiliates.
91
Slide 128
Slide 128 text
MySQL REST Service (MRS) (3)
Do you remember the JOIN between our collection and two tables ?
Copyright @ 2025 Oracle and/or its affiliates.
92
Slide 129
Slide 129 text
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
Slide 130
Slide 130 text
MySQL REST Service (MRS) (4)
Copyright @ 2025 Oracle and/or its affiliates.
93
Slide 131
Slide 131 text
MySQL REST Service (MRS) (5)
Copyright @ 2025 Oracle and/or its affiliates.
94
Slide 132
Slide 132 text
Conclusion
what do I gain ?
Copyright @ 2025 Oracle and/or its affiliates.
95
Slide 133
Slide 133 text
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
Slide 134
Slide 134 text
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
Slide 135
Slide 135 text
Conclusion (2)
And with MRS, MySQL serves data to client applications via a
HTTPS REST interface.
Copyright @ 2025 Oracle and/or its affiliates.
97
Slide 136
Slide 136 text
NoSQL + SQL = MySQL
Copyright @ 2025 Oracle and/or its affiliates.
98
Slide 137
Slide 137 text
NoSQL + SQL = MySQL
Copyright @ 2025 Oracle and/or its affiliates.
98
Slide 138
Slide 138 text
Share your to MySQL
#mysql #MySQLCommunity
Join our slack channel!
bit.ly/mysql-slack
Copyright @ 2025 Oracle and/or its affiliates.
99
Slide 139
Slide 139 text
Questions ?
Copyright @ 2025 Oracle and/or its affiliates.
100