MySQL New York Meetup: MySQL 8.0 Document Store - How to mix NoSQL & SQL in MySQL 8.0

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 1/137 1 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 2/137 2 / 1372 / 1372 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 3/137 MyMySQLSQL 8.08.0 a Document Store with all the benefits of a transactional RDBMSa Document Store with all the benefits of a transactional RDBMS Frédéric Descamps - MySQL Community Manager - Oracle 3 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 4/137 Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purpose only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied up in making purchasing decisions. The development, release and timing of any features or functionality described for Oracle´s product remains at the sole discretion of Oracle. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 4 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 5/137 about me - http://about.me/lefred Who am I ?Who am I ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 5 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 6/137 Frédéric Descamps @lefred MySQL Evangelist Hacking MySQL since 3.23 devops believer living in Belgium 🇧🇪 http://lefred.be Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 6 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 8/137 http://bit.ly/NY-MySQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 8 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 9/137 Why ? Developers don´t really like SQLSQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 9 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 10/137 Why ? SQL can be complicated and slows down the initial development Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 10 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 11/137 easy operations What do developers want ?What do developers want ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 11 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 12/137 Use Objects / Documents Developers want to just use objects (that's why they usually love what DBAs hates: ORMs !). Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 12 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 13/137 Use Objects / Documents Developers want to just use objects (that's why they usually love what DBAs hates: ORMs !). They want to deal with these objects easily (CRUDCRUD operations) and they don't want to think about schema design (slows down the initial development process). Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 13 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 14/137 Use Objects / Documents Developers want to just use objects (that's why they usually love what DBAs hates: ORMs !). They want to deal with these objects easily (CRUDCRUD operations) and they don't want to think about schema design (slows down the initial development process). But they also want to keep their data safe and use transactions. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 14 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 15/137 RDBMS & MySQL Relational DatabasesRelational Databases Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 15 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 16/137 Relational Databases Data Integrity Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 16 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 17/137 Relational Databases Data Integrity normalization Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 17 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 18/137 Relational Databases Data Integrity normalization constraints (foreign keys, ...) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 18 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 19/137 Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 19 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 20/137 Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability ACID compliant Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 20 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 21/137 Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability ACID compliant transactions Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 21 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 22/137 Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability ACID compliant transactions SQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 22 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 23/137 Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability ACID compliant transactions SQL powerfull query language Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 23 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 24/137 NoSQL & MySQL NoSQL DatabasesNoSQL Databases Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 24 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 25/137 NoSQL or Document Store Schemaless Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 25 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 26/137 NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 26 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 27/137 NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 27 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 28/137 NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 28 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 29/137 NoSQL or 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 29 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 30/137 NoSQL or 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 30 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 31/137 NoSQL or 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 oobject-oriented Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 31 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 32/137 NoSQL or 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 oobject-oriented JSON Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 32 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 33/137 NoSQL or 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 oobject-oriented JSON close to frontend Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 33 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 34/137 NoSQL or 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 oobject-oriented JSON close to frontend native in JS Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 34 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 35/137 NoSQL or 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 oobject-oriented JSON close to frontend native in JS easy to learn Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 35 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 36/137 How DBAs see data Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 36 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 37/137 How DBAs see data How Developers see data { "GNP" : 249704, "Name" : "Belgium", "government" : { "GovernmentForm" : "Constitutional Monarchy, Federation", "HeadOfState" : "Philippe I" }, "_id" : "BEL", "IndepYear" : 1830, "demographics" : { "Population" : 10239000, "LifeExpectancy" : 77.8000030517578 }, "geography" : { "Region" : "Western Europe", "SurfaceArea" : 30518, "Continent" : "Europe" } } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 37 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 38/137 What if there was a way to provide both SQL and NoSQL on one stable platform that has proven stability on well know technology with a large Community and a diverse ecosystem ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 38 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 39/137 DBMS or NoSQL ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 39 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 40/137 DBMS or NoSQL ? Why not both ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 40 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 41/137 The MySQL Document Store ! SQL is now optional ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 41 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 42/137 SQL is now optional ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 42 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 43/137 Developers:Developers: [x] schemaless [x] rapid prototying/simpler APIs [x] document model [x] transactions Operations:Operations: [x] performance management/visibility [x] robust replication, backup, restore [x] comprehensive tooling ecosystem [x] simpler application schema upgrades Business Owner:Business Owner: [x] don't lose my data == ACID trx [x] capture all my data = extensible/schemaless [x] product on schedule/time to market = rapid developement A solution for all Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 43 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 44/137 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 44 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 45/137 MySQL Document Store the Solutionthe Solution Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 45 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 46/137 Built on the MySQL JSON Data type and Proven MySQL Server Technology Provides a schema flexible JSON Document Store NoNo SQL required No need to define all possible attributes, 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 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 46 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 47/137 X DevAPI Connectors for C++, Java, .Net, Node.js, Python, PHP working with Communities to help them supporting it too New MySQL Shell Command Completion Python, JavaScrips & SQL modes Admin functions New Util object A new high-level session concept that can scale from single MySQL Server to a multiple server environment Non-blocking, asynchronous calls follow common language patterns Supports CRUD operations Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 47 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 48/137 Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 48 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 49/137 Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 49 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 52/137 Let´s query Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 52 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 53/137 Let´s query That´s too much records to show it here... let´s limit it Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 53 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 55/137 Some more examples Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 55 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 56/137 Some more examples Let´s add a selection criteria: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 56 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 57/137 Using IN... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 57 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 58/137 Syntax slightly different than MongoDB Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 58 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 59/137 Syntax slightly different than MongoDB > db.restaurants. nd({"cuisine": "French", "borough": { $not: /^Manhattan/} }, {"_id":0, "name": 1,"cuisine": 1, "borough": 1}).limit(2) { "borough" : "Queens", "cuisine" : "French", "name" : "La Baraka Restaurant" } { "borough" : "Queens", "cuisine" : "French", "name" : "Air France Lounge" } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 59 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 60/137 And for developers ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 60 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 61/137 And for developers ? $session = mysql_xdevapigetSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection-> nd($search)->execute()->fetchAll(); ... foreachforeach ($results asas $doc) { echoecho "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echoecho "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 61 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 62/137 And for developers ? $session = mysql_xdevapigetSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection-> nd($search)->execute()->fetchAll(); ... foreachforeach ($results asas $doc) { echoecho "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echoecho "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Easy, using only CRUD operations ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 62 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 63/137 CRUD operations Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 63 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 64/137 CRUD operations for collections Add a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 64 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 65/137 CRUD operations for collections Add a document collection.add({ name: 'fred', age: 42 }) .add({ name: 'dave', age: 23 }) .execute() collection.add([ { name: 'dimo', age: 50 }, { name: 'kenny', age: 25 } ]).execute() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 65 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 66/137 CRUD operations for collections Modify a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 66 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 67/137 CRUD operations for collections Modify a document collection.modify('name = :name') .bind('name', 'fred') .set('age', 43) .sort('name ASC') .limit(1) .execute() collection.modify('name = :name') .bind('name', 'fred') .patch({ age: 43, active: false }) .sort('name DESC') .limit(1) .execute() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 67 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 68/137 CRUD operations for collections Remove a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 68 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 69/137 CRUD operations for collections Remove a document collection.remove('name = :name') .bind('name', 'fred') .sort('age ASC') .limit(1) .execute() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 69 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 70/137 MySQL Document Store Objects Summary Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 70 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 71/137 All you need to know is here: https://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 71 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 72/137 we do care about your data MyMySQLSQL Document StoreDocument Store is Full ACID Compliantis Full ACID Compliant Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 72 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 73/137 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 73 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 74/137 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 74 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 75/137 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 75 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 76/137 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 76 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 77/137 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ- COMMITTED|... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 77 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 78/137 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ- COMMITTED|... We do care about your data ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 78 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 79/137 MySQL DS Full ACID - Transactions support Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 79 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 80/137 MySQL DS Full ACID - Transactions support Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 80 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 81/137 OK we have Document Store, CRUD and ACID but what makesbut what makes MyMySQLSQL Document StoreDocument Store unique ?unique ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 81 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 82/137 Challenge: list the best restaurant of each type of food and show the top 10, with the best one first ! don't forget that all these restaurants are just JSON documents Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 82 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 83/137 NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 83 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 86/137 what about my old SQL ? The hidden part of the icebergThe hidden part of the iceberg Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 86 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 87/137 JSON datatype is behind the scene native datatype (since 5.7.8) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 87 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 88/137 JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 88 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 89/137 JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 89 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 90/137 JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH, JSON_TABLES, ...) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 90 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 91/137 JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH, JSON_TABLES, ...) Generated/virtual columns Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 91 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 92/137 JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH, JSON_TABLES, ...) Generated/virtual columns Indexing JSON data Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 92 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 93/137 JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH, JSON_TABLES, ...) Generated/virtual columns Indexing JSON data Foreign Keys to JSON data Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 93 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 94/137 JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH, JSON_TABLES, ...) Generated/virtual columns Indexing JSON data Foreign Keys to JSON data SQL Views to JSON data Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 94 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 95/137 How does the MySQL Document Store work ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 95 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 96/137 What does a collection look like on the server ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 96 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 97/137 _id Every document has a unique identifier called the document ID, which can be thought of as the equivalent of a table´s primary key. The document ID value can be manually assigned when adding a document. If no value is assigned, a document ID is generated and assigned to the document automatically ! Use getDocumentId() or getDocumentIds() to get _ids(s) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 97 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 98/137 SQL and JSON Example Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 98 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 99/137 SQL and JSON Example same as: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 99 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 100/137 SQL and JSON Example same as: we can use it in SQL: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 100 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 101/137 SQL and JSON Example (2): explain Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 101 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 102/137 SQL and JSON Example (2): explain Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 102 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 103/137 SQL and JSON Example (3): add index Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 103 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 104/137 SQL and JSON Example (3): add index Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 104 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 105/137 Indexes in Document Store It's also possible to create indexes without using SQL syntax: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 105 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 106/137 Indexes in Document Store It's also possible to create indexes without using SQL syntax: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 106 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 107/137 SQL and JSON Example (5): arrays Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 107 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 111/137 NoSQL as SQL JSON_TABLEJSON_TABLE Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 111 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 112/137 NoSQL as SQL (2) JSON_TABLEJSON_TABLE Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 112 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 114/137 SQL and NoSQL - JOINS Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 114 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 117/137 SQL and NoSQL - JOINS (2) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 117 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 120/137 SQL and NoSQL - and back to JSON Document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 120 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 121/137 NoSQL or SQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 121 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 122/137 MySQL Shell is for DBAs and Developers Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 00:05 122 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 123/137 what do I gain ? ConclusionConclusion Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 123 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 124/137 schemaless flexible data structure easy to start (CRUD) Conclusion This is the best of the two worlds in one product ! Data integrity ACID Compliant Transactions SQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 124 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 127/137 http://lefred.be/content/top-10-reasons-for- nosql-with-mysql/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 127 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 131/137 Some more examples Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 131 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 132/137 Some more examples Let's add a selection criteria: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 132 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 133/137 Using IN... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 133 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 134/137 And for developers ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 134 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 135/137 And for developers ? $session = mysql_xdevapigetSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection-> nd($search)->execute()->fetchAll(); ... foreachforeach ($results asas $doc) { echoecho "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echoecho "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 135 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 136/137 And for developers ? $session = mysql_xdevapigetSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection-> nd($search)->execute()->fetchAll(); ... foreachforeach ($results asas $doc) { echoecho "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echoecho "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Easy, using only CRUD operations ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 136 / 137

3/9/2019 MySQL New York Meetup - MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 ﬁle:///home/fred/ownCloud/Presentations/ORACLE/NEW%20YORK%202019/MySQL%20Meetup/MySQL%20Document%20Store.html#134 137/137 Thank you ! Any Questions ? share your 💕 for MySQL on social media using @mysql #MySQL8isGreat #MySQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 137 / 137

MySQL New York Meetup: MySQL 8.0 Document Store - How to mix NoSQL & SQL in MySQL 8.0

More Related Content

What's hot

Similar to MySQL New York Meetup: MySQL 8.0 Document Store - How to mix NoSQL & SQL in MySQL 8.0

More from Frederic Descamps

Recently uploaded

MySQL New York Meetup: MySQL 8.0 Document Store - How to mix NoSQL & SQL in MySQL 8.0