Combining SQL and NoSQL with MySQL

[Pages:30]Combining SQL and NoSQL with MySQL

Manyi Lu Director MySQL Optimizer Team, Oracle April, 2018

CCooppyyrriigghhtt??22001164,,OOrraaccleleaanndd//oorrititssaafffiliilaiatteess..AAlllrrigighhttssrreesseerrvveedd..

Safe Harbor Statement

The following is intended to outline our general product direction. It is intended for information purposes 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 upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle.

Copyright ? 2016, Oracle and/or its affiliates. All rights reserved.

2

Agenda

JSON data type

JSON functions Indexing JSON data Partial update of JSON

Copyright ? 2016, Oracle and/or its affiliates. All rights reserved.

3

The New JSON Datatype

? Validation on INSERT

CREATE TABLE employees (data JSON);

? No reparsing on SELECT

INSERT INTO employees VALUES

('{"id": 1, "name": "Jane"}'), ('{"id": 2, "name": "Joe"}');

? Optimized for read

? Dictionary of sorted keys

SELECT * FROM employees; +-------------------------------------+

?

Can compare JSON/SQL

| data

| ? Can convert JSON/SQL

+-------------------------------------+ ? Supports all native

| {"id": 1, "name": "Jane"}

| JSON datatypes

| {"id": 2, "name": "Joe"}

| ? Also supports date,

+-------------------------------------+ time, timestamp etc.

Copyright ? 2016, Oracle and/or its affiliates. All rights reserved.

4

Agenda

JSON data type

JSON functions

Indexing JSON data Partial update of JSON

Copyright ? 2016, Oracle and/or its affiliates. All rights reserved.

5

JSON Functions

MySQL 5.7 and 8.0

JSON_ARRAY_APPEND() JSON_ARRAY_INSERT() JSON_ARRAY() JSON_CONTAINS_PATH() JSON_CONTAINS() JSON_DEPTH() JSON_EXTRACT() JSON_INSERT() JSON_KEYS() JSON_LENGTH()

JSON_MERGE[_PRESERVE]() JSON_OBJECT() JSON_QUOTE() JSON_REMOVE() JSON_REPLACE() JSON_SEARCH() JSON_SET() JSON_TYPE() JSON_UNQUOTE() JSON_VALID()

JSON_PRETTY() JSON_STORAGE_SIZE() JSON_STORAGE_FREE() JSON_ARRAYAGG() JSON_OBJECTAGG() JSON_MERGE_PATCH() JSON_TABLE()

Copyright ? 2016, Oracle and/or its affiliates. All rights reserved.

6

Inlined JSON Path Expressions

[[database.]table.]column->"$"

SELECT * FROM employees WHERE data->"$.name" = "Jane"; Is a short hand for SELECT * FROM employees WHERE JSON_EXTRACT(data, "$.name" ) = "Jane";

? SELECT * FROM employees WHERE data->'$.id'= 2; ? ALTER ... ADD COLUMN id INT AS (data->'$.id') ... ? CREATE VIEW .. AS SELECT data->'$.id', data->'$.name' FROM ...

Copyright ? 2016, Oracle and/or its affiliates. All rights reserved.

7

JSON_TABLE

? JSON is not limited to CRUD, can also be used in complex queries ? JSON_TABLE creates a relational view of JSON data

? Each object in a JSON array as a row ? JSON values within an JSON object as column values

? Query the result of JSON_TABLE() as a relational table using SQL ? Leverage existing framework for aggregation

Copyright ? 2016, Oracle and/or its affiliates. All rights reserved.

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download