MySQL JSON Functions

한국 MySQL 사용자그룹

김영하

Agenda

Why JSON in MySQL?

What is JSON?

MySQL JSON Functions Overview

Where to Test?

Real Example

나머지

Why JSON in MySQL?

패러다임의 변화

Data Sources

Protovis.js → D3.js

Data Visualization

Python or R

1. collecting

Data Analysis

3. combining & comparing

2. storaging

4. visualizing

What is JSON?

JSON is ...

JavaScript Object Notation

the creation of Douglas Crockford

2009.12, 공식적으로 ECMA 표준 ECMA-404로 인정됨

ECMAScript-262, 5th ed의 표준화에 반영이 됨

the data interchange standard

Data Property의 기본형식 : "name":"value"

{"name":"MySQL"}

JSON SYNTAX

Data property들은 comma(,)로 구분{"name":"MySQL","likes":"JSON"}

Data object들의 배열[{"name":"Lawrence","likes":"JSON"},{"name":"John","likes":"JQUERY"}]

string
boolean
number
null
array
object

JSON Data Type

Why JSON?

JSON Is a Data Interchange Format

JSON Is Programming Language Independent

JSON Is Based on JavaScript Object Literals

JSON can use Arrays
JSON is shorter
JSON works with more complex data
JSON is better standardized than CSV
No end tags are required for JSON

JSON vs XML vs CSV

ALL

Can store data as values within values
Can be used across multiple programming languages
Can be fetched and parsed to be used within the code

{
title : "This is my title.",
body : "This is the body."
}

{
'title': 'This is my title.',
'body': 'This is the body.'
}

{
"title": "This is my title.",
"body": "This is the body."
}

Quiz!

어떤 것이 진짜 JSON 형태일까요?

JSON Formatter & Validator (http://jsonformatter.curiousconcept.com)

JSON Editor Online (https://www.jsoneditoronline.org)

JSONLint (http://jsonlint.com)

JSON Validator

MySQL JSON functions overview

Support JSON in other RDBMS

Oracle 12c
mariaDB 5.3
MSSQL 2016
DB2 10.5

JSON Parser for JSON input
Support for all JSON datatypes

        – NULL
        – Numeric
        – Boolean

Support for JSON arrays
Better indexing without resorting to virtual columns
More JSON manipulation functions
A proper JSON datatype

        –Enforced UTF8
        –JSON even in the SCHEMA
        –Default JSON output format
          To SELECT a JSON column without having to resort to COLUMN_JSON to get JSON out

아직 부족한 것들

MySQL Functions List

json_array()
json_object()
json_insert()
json_remove()
json_set()
json_replace()
json_append()
json_merge()
json_extract()

json_search()
json_contains()
json_contains_path()
json_valid()
json_type()
json_keys()
json_length()
json_depth()
json_unquote()
json_quote()

Where to Test?

https://hub.docker.com

docker@mysqlserver:~$ docker pull mysql:5.7.8
docker@mysqlserver:~$ docker run --name mysqljsonfunctions \
                      -e MYSQL_ROOT_PASSWORD=admin -d mysql:5.7.8
fc6790aa50896acda92d9ee380af3683339f2675f670cff36ffc9e0221b67b7a
docker@mysqlserver:~$ docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
fc6790aa5089        mysql:5.7.8         "/entrypoint.sh mysql"   4 seconds ago       Up 3 seconds        3306/tcp            mysqljsonfunctions
docker@mysqlserver:~$ docker run -it --link mysqljsonfunctions:mysql --rm mysql:5.7.8 \
                      sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"3306" -uroot -p'
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

Real Example

http://mysqlserverteam.com/json-labs-release-json-functions-part-1-manipulation-json-data/

json_array()

json_object()

json_insert()

json_remove()

json_set()

json_replace()

json_append()

json_merge()

json_extract()

mysql> create table thermostat_model 
    -> (model_id varchar(50) primary key, capabilities json);
Query OK, 0 rows affected (0.21 sec)

mysql> create table thermostat_reading( reading json );
Query OK, 0 rows affected (0.22 sec)