mysql json functions
DESCRIPTION
MySQL JSON functions which I presented at MySQL Connect 2013. For updates about JSON functions visit https://blogs.oracle.com/svetasmirnova/tags/jsonTRANSCRIPT
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.2
Insert Picture HereMySQL JSON FunctionsSveta SmirnovaPrincipal Technical Support Engineer
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.3
Program Agenda
Introduction: NoSQL involvement on MySQL Overview of the functions Function descriptions Where to get The future
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.4
Insert Chart HereMain historic points of NoSQL
1997 1998 2000 2003 2004 2008 2009 2010 2012 2013
0
0.5
1
1.5
2
2.5
3
3.5
4
Databases
NoSQL history
NoSQLdatabase
NoSQLterm
db40 memcachedBigTable
MemcacheDBTarantoolCouchDB
MongoDBRedis
WebSphere
VirtuosoDynamoDB
Hbase
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.5
Insert Chart HereMain points for NoSQL features in MySQL
20092010
20112012
2013
0
1
2
3
4
5
6
NoSQL in MySQL world
Memcached bugsat mysql.com
HandlerSocket
InnoDB with memcachedMemcache API for MySQL Cluster
NoSQL Connectorfor JavaScriptEXPLAIN in JSON
Hadoop Appliermysqlv8udfsJSON UDFs
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.6
Insert Chart HereA little bit before this year
2012
2013
00.020.040.060.080.1
0.120.140.160.180.2
JSON functions in MySQL
Version 0.1Never published
Version 0.2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.7
JSON functions overview
FunctionsManipulate JSON text
● Validate● Search● Modify
UDF functions● Easy to install● Independent from MySQL server version
Work on all MySQL supported platformsBinaries for Linux, Mac OSX 7 and Windows
What are they doing?
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.8
How to install?UNIX
create function json_valid returns integersoname 'libmy_json_udf.so';create function json_search returns stringsoname 'libmy_json_udf.so';create function json_extract returns stringsoname 'libmy_json_udf.so';create function json_replace returns stringsoname 'libmy_json_udf.so';create function json_append returns stringsoname 'libmy_json_udf.so';...
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.9
How to install?Windows
create function json_remove returns string soname 'my_json_udf.dll';create function json_set returns string soname 'my_json_udf.dll';create function json_merge returns string soname 'my_json_udf.dll';create function json_contains_key returns integersoname 'my_json_udf.dll';create function json_test_parser returns stringsoname 'my_json_udf.dll';...
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.10
Dependencies
Regex libraryUNIX
● Usually already existsWindows
● Provided with sources● Compiled statically
You don't need to install additional libraries to run the UDF!
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.11
How to compile?UNIX
You need:● cmake● Regex library (usually already exists)● Working compiler
To build:● cmake . DMYSQL_DIR=/home/sveta/src/mysql5.5● make
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.12
How to compile?Windows
You need: PCRE static libraries, Visual Studio and cmake (cmake.org)To build PCRE:
● Download sources from http://www.pcre.org/ Recommended version is 8.33 or cd pcre8.33● Unpack archive and run:
● "C:\Program Files (x86)\CMake 2.8\bin\cmake.exe" G "Visual Studio 11 Win64"
● devenv PCRE.sln /build Release
● To build JSON UDFs copy pcre.h, pcreposix.h, Release/pcre.lib, Release/pcreposix.lib into JSON UDFs source directory, then run:
● "C:\Program Files (x86)\CMake 2.8\bin\cmake.exe" G "Visual Studio 11 Win64" . DMYSQL_DIR="C:/MySQL/mysql5.5"
● devenv my_json_udf.sln /build Release
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.13
Disadvantages of UDFs
They are slow● Can not use certain server features, available for internal functions
● Full-text
● Indexes
● Built-in optimization
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.14
Why UDFs?
Flexibility● You can install as many functions as you want: single, few or all of them
Compatible with any server version● You don't need to upgrade to not stable version only to try these functions
Easy to addEasy to changeEasy to removeFeature requests are easy to implement
● Report bugs!● Raise your opinion!
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.15
Insert Picture Here
Functions descriptions
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.16
json_valid(doc)
Checks if doc is valid JSON document.Returns 1 if document is valid, 0 if document is invalid.Strict format as described at http://json.org
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.17
json_valid(doc)
mysql> select json_valid('{"MySQL connect": ["conference", 2013]}');++| json_valid('{"MySQL connect": ["conference", 2013]}') |++| 1 |++1 row in set (0.01 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.18
json_valid(doc)
mysql> select json_valid('{"MySQL connect"}');++| json_valid('{"MySQL connect"}') |++| 0 |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.19
json_contains_key(doc, keypart1, keypart2, ...)
Checks if documents contains specified key.Returns 1 if key exists, 0 if not exists or NULL if parsing failed.Warning! This version does not check whole document for validity.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.20
json_contains_key(doc, keypart1, keypart2, ...)
SET optimizer_trace=1;mysql> select user from mysql.user;++...mysql> select json_contains_key(trace, 'steps', '0', 'join_optimization', 'steps', '0', 'condition_processing') as contains from information_schema.optimizer_trace;++| contains |++| 0 |++1 row in set (0.01 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.21
json_contains_key(doc, keypart1, keypart2, ...)
mysql> select user from mysql.user where user='Sveta';++...mysql> select json_contains_key(trace, 'steps', '0', 'join_optimization', 'steps', '0', 'condition_processing') as contains from information_schema.optimizer_trace;++| contains |++| 1 |++1 row in set (0.01 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.22
json_extract(doc, keypart1, keypart2, ...)
Extracts value of the specified key.Returns value of the key specified, NULL if the key does not exist or if parsing failed.Warning! This version does not check whole document for validity.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.23
json_extract(doc, keypart1, keypart2, ...)
SET optimizer_trace=1;mysql> select user from mysql.user;++...mysql> select json_extract(trace, 'steps', '0', 'join_optimization', 'steps', '0', 'condition_processing') as value from information_schema.optimizer_trace;++| value |++| NULL |++1 row in set (0.01 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.24
Search path
{“steps”: [ {“join_optimization”: {“steps”: [ {“condition_processing”: .....
json_extract(trace, 'steps', '0', 'join_optimization', 'steps', '0', 'condition_processing')
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.25
json_extract(doc, keypart1, keypart2, ...)
mysql> select user from mysql.user where user='Sveta';++...mysql> select json_extract(trace, 'steps', '0', 'join_optimization', 'steps', '0', 'condition_processing') as value from information_schema.optimizer_trace\G*************************** 1. row ***************************value: { "condition": "WHERE", "original_condition": "(`mysql`.`user`.`User` = 'sveta')", "steps": [....
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.26
json_append(doc, keypart1, keypart2, ..., new_element)
Inserts new element into JSON document.Returns document with appended element, original document if no place to insert or NULL if parsing failed.Warning! This version does not check whole document for validity.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.27
json_append(doc, keypart1, keypart2, ..., new_element)
mysql> select json_append('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '2', '”San Francisco”') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["conference", 2013, “San Francisco”]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.28
json_append(doc, keypart1, keypart2, ..., new_element)
mysql> select json_append('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '1', '”San Francisco”') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["conference", 2013]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.29
json_replace(doc, keypart1, keypart2, ..., new_value)
Updates value of the specified key.Returns document with replaced key or original document if no such an element found, NULL if parsing failed.Warning! This version does not check whole document for validity.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.30
json_replace(doc, keypart1, keypart2, ..., new_value)
mysql> select json_replace('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '0', '"User conference"') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["User conference", 2013]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.31
json_replace(doc, keypart1, keypart2, ..., new_value)
mysql> select json_replace('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '2', '"User conference"') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["conference", 2013]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.32
json_set(doc, keypart1, keypart2, ..., new_value)
Performs kind of INSERT ... ON DUPLICATE KEY UPDATE operation.Returns document with updated or inserted element or NULL if parsing failed. Warning! This version does not check whole document for validity.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.33
json_set(doc, keypart1, keypart2, ..., new_value)
mysql> select json_set('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '0', '"User conference"') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["User conference", 2013]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.34
json_set(doc, keypart1, keypart2, ..., new_value)
mysql> select json_set('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '2', '"San Francisco"') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["conference", 2013, "San Francisco"]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.35
json_remove(doc, keypart1, keypart2, ...)
Removes element specified by the key.Returns document without the element, original document if no element found or NULL if parsing failed. Warning! This version does not check whole document for validity.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.36
json_remove(doc, keypart1, keypart2, ...)
mysql> select json_remove('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '1') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["conference"]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.37
json_remove(doc, keypart1, keypart2, ...)
mysql> select json_remove('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '2') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["conference", 2013]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.38
json_search(doc, value)
Searches for specified value in the document.Returns key path of the element which contains the value in reverse order or NULL if not found or parsing failed.Warning! This version does not check whole document for validity.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.39
json_search(doc, value)
mysql> select json_search(trace, '"trivial_condition_removal"') from information_schema.optimizer_trace;+| json_search(trace, '"trivial_condition_removal"') |++| transformation:0:steps:condition_processing:0:steps:join_optimization:0:steps:: |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.40
json_search(doc, value)
mysql> select json_search(trace, '"trivial_condition"') from information_schema.optimizer_trace;++| json_search(trace, '"trivial_condition"') |++| NULL |++1 row in set (0.01 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.41
json_merge(doc1, doc2, ...)
Merges 2 or more documents into one.Returns first document with following documents appended.Warning! This version does not check whole document for validity.If one of following documents does not contain an opening curly bracket first documents merged are returned and warning is generated.NULL if first document does not contain an opening curly bracket.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.42
json_merge(doc1, doc2, ...)
mysql> select json_merge('{"MySQL connect": ["conference", 2012]}', '{"MySQL connect": ["conference", 2013]}') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["conference", 2012], "MySQL connect": ["conference", 2013]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.43
json_merge(doc1, doc2, ...)
mysql> select json_merge('{"MySQL connect": ["conference", 2012]}', '{"MySQL connect": ["conference", 2013]}', '1') as 'MySQL connect';++| MySQL connect |++| {"MySQL connect": ["conference", 2012], "MySQL connect": ["conference", 2013]} |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.44
json_test_parser(doc)
Returns text representation of parse tree of the JSON document, partial parse tree or empty string if document is invalid.This function is supposed to use for tests only and should not be used in production.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.45
json_test_parser(doc)
mysql> select json_test_parser('{"MySQL connect": ["conference", 2013]}') as 'Parse tree'\G********************** 1. row **********************Parse tree: => "conference"; => 2013; "MySQL connect" => ["conference", 2013]; => {"MySQL connect": ["conference", 2013]};
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.46
json_test_parser(doc)
mysql> select json_test_parser('{"MySQL connect": ["conference", 2013]') as 'Parse tree';++| Parse tree |++| => "conference"; => 2013;"MySQL connect" => ["conference", 2013]; |++1 row in set (0.00 sec)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.47
Insert Picture Here
Where to get
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.48
Source code and binaries
MySQL Labs● Source code● Binaries
● x86 and x86_64
● Generic Linux
● Mac OSX 10.7
● Windows 7
● http://labs.mysql.com/
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.49
More information
Manuals and articles● README file● My blog: https://blogs.oracle.com/svetasmirnova/
Announces● My twitter: https://twitter.com/#!/svetsmirnova
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.50
Insert Picture Here
The future
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.51
Depends from you
Send bugsSend feature requestsMore you send – more ideas we implementNow you can affect decisions
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.52
Where to report bugs and feature requests
MySQL Community bugs database● https://bugs.mysql.com/● No special category for now● General category
● “MySQL Server: User-defined functions (UDF)”
Internal Oracle bugs database
● Ask MySQL Support engineer to open a bug report for you
● Category “UDFJSON”
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.53
More NoSQLSessions at MySQL Connect
MySQL and Hadoop: Big Data Integration—Unlocking New Insights [CON2053, Sunday, 11:30 AM]MySQL User-Defined Functions....in JavaScript! [CON1738, passed]Oracle NoSQL Database: When Is It the Right Tool for the Job? [CON13034, Saturday, 4:00 PM]MySQL As a NoSQL Store with the InnoDB/memcached Plug-in [CON3457, Sunday, 1:00 PM]MySQL’s EXPLAIN Command New Features [HOL9734, passed]Big Data with MySQL and Hadoop [CON2342, Sunday, 5:30 PM]
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.54
References
https://blogs.oracle.com/svetasmirnova/https://twitter.com/#!/svetsmirnovahttp://json.org/http://www.pcre.org/http://dev.mysql.com/doc/refman/5.6/en/adding-functions.htmlhttp://bugs.mysql.com/https://support.oracle.com
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.56
Insert Picture Here
THANK YOU!
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.58
The preceding 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.