�ݺ�ߣ

Copyright ? 2018, Oracle and/or its affiliates. All rights reserved. |
JSON_TABLE
?ystein Gr?vlen
Optimizer Geek
Oracle
April 24, 2018
The Best of Both Worlds

JSON Data Type
CREATE TABLE t1(json_col JSON);
INSERT INTO t1 VALUES (
'{ "people": [
{ "name":"John Smith", "address":"780 Mission St, San Francisco, CA 94103"},
{ "name":"Sally Brown", "address":"75 37th Ave S, St Cloud, MN 94103"},
{ "name":"John Johnson", "address":"1262 Roosevelt Trail, Raymond, ME 04071"}
] }'
);
2

JSON_TABLE
SELECT people.*
FROM t1,
JSON_TABLE(json_col, '$.people[*]' COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')) people;
3
Convert JSON documents to relational tables
name address
John Smith 780 Mission St, San Francisco, CA 94103
Sally Brown 75 37th Ave S, St Cloud, MN 9410
John Johnson 1262 Roosevelt Trail, Raymond, ME 04071

JSON_TABLE
SELECT people.*
FROM t1,
WHERE people.name LIKE 'John%';
4
Filter JSON data
name address
John Smith 780 Mission St, San Francisco, CA 94103
John Johnson 1262 Roosevelt Trail, Raymond, ME 04071

Construct a New JSON Document
SELECT JSON_OBJECT("people",
JSON_ARRAYAGG(JSON_OBJECT("name", name, "address", address))) json_doc
FROM t1,
WHERE people.name LIKE 'John%';
5
json_doc
{"people": [{"name": "John Smith", "address": "780 Mission St, San Francisco,
CA 94103"}, {"name": "John Johnson", "address": "1262 Roosevelt Trail,
Raymond, ME 04071"}]}

Copyright ? 2018, Oracle and/or its affiliates. All rights reserved. | 6
JSON_TABLE
id father married child_id child age
1 John 1 1 Eric 12
1 John 1 2 Beth 10
2 Paul 0 1 Sarah 9
2 Paul 0 2 Noah 3
2 Paul 0 3 Peter 1
[
{ "father":"John", "mother":"Mary",
"marriage_date":"2003-12-05",
"children": [
{ "name":"Eric", "age":12 },
{ "name":"Beth", "age":10 } ] },
{ "father":"Paul", "mother":"Laura",
"children": [
{ "name":"Sarah", "age":9},
{ "name":"Noah", "age":3} ,
{ "name":"Peter", "age":1} ] }
]
Nested arrays

Copyright ? 2018, Oracle and/or its affiliates. All rights reserved. | 7
JSON_TABLE �C Nested Arrays
id father married child_id child age
1 John 1 1 Eric 12
1 John 1 2 Beth 10
2 Paul 0 1 Sarah 9
2 Paul 0 2 Noah 3
2 Paul 0 3 Peter 1
JSON_TABLE (families, '$[*]' COLUMNS (
id FOR ORDINALITY,
father VARCHAR(30) PATH '$.father',
married INTEGER EXISTS PATH
'$.marriage_date',
NESTED PATH '$.children[*]' COLUMNS (
child_id FOR ORDINALITY,
child VARCHAR(30) PATH '$.name',
age INTEGER PATH '$.age') ) )

JSON_TABLE
SELECT father, COUNT(*) "#children", AVG(age) "age average"
FROM t, JSON_TABLE (families, '$[*]' COLUMNS (
id FOR ORDINALITY,
father VARCHAR(30) PATH '$.father',
NESTED PATH '$.children[*]' COLUMNS (age INTEGER PATH '$.age' ) ) ) AS fam
GROUP BY id, father;
8
SQL aggregation on JSON data
father #children age average
John 2 11.0000
Paul 3 4.3333

Put Computed Data Back Into JSON Document
SELECT JSON_ARRAYAGG(fam_obj) families
FROM (
SELECT JSON_MERGE_PATCH(family,
JSON_OBJECT("#children", COUNT(*), "avg_age" , AVG(age))) fam_obj
id FOR ORDINALITY,
family JSON PATH '$',
NESTED PATH '$.children[*]' COLUMNS (age INTEGER PATH '$.age' ) )
) fam
GROUP BY id, family) fams;
9

Modify JSON Objects
SELECT
JSON_ARRAYAGG(JSON_MERGE_PATCH(JSON_OBJECT("id", id), family))
AS families
id FOR ORDINALITY,
family JSON PATH '$')) fam;
10
Put IDs in objects
families
[{"id": 1, "father": "John", "mother": "Mary", "children": [{"age": 12, "name":
"Eric"}, {"age": 10, "name": "Beth"}], "marriage_date": "2003-12-05"},
{"id": 2, "father": "Paul", "mother": "Laura", "children": [{"age": 9, "name":
"Sarah"}, {"age": 3, "name": "Noah"}, {"age": 1, "name": "Peter"}]}]

Want to Learn More About New and Upcoming SQL
Features?
? MySQL 8.0: What is New in Optimizer and Executor?
�C Manyi Lu
�C Tuesday 4:50PM-5:15PM; Room GS
? Running JavaScript Stored-Programs Inside MySQL Server
�C ?ystein Gr?vlen, Vojin Jovanovic, Farhan Tauheed
�C Wednesday 11:00AM-11:50AM; Room GS
11
Presentations at Percona Live

�ݺ�ߣ

JSON_TABLE -- The best of both worlds

More Related Content

JSON_TABLE -- The best of both worlds