Skip to content
Find file
Fetching contributors…
Cannot retrieve contributors at this time
580 lines (580 sloc) 20.1 KB
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" type="text/css" href="../mysqludf.css"/>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>lib_mysql_json - A library of MySQL UDFs for working with JSON</title>
</head>
<body>
<div>
<a href="../index.html">Top</a>
| <a href="../mysql_udf_repository_libraries.html">Up</a>
</div>
<h1>lib_mysqludf_json</h1>
<div>
<a href="lib_mysqludf_json.html">Documentation</a>
| <a href="lib_mysqludf_json.so">Binary</a>
| <a href="lib_mysqludf_json.sql">Installation</a>
| <a href="lib_mysqludf_json.c">Source</a>
</div>
<p>
<a href="http://www.json.org/" target="_json">JSON</a> is an abbreviation of JavaScript Object Notation.
JSON uses a subset of the <a href="http://www.ecma-international.org/publications/standards/Ecma-262.htm" target="_ecma">ecmascript</a>
(javascript) language to denote javascript data structures (see also <a href="http://tools.ietf.org/html/rfc4627" target="_ietf">RFC 4627</a>).
As such, JSON is a text-oriented data format.
</p>
<p>
JSON is particularly useful as a format for data exchange.
It is mainly used in AJAX web applications as an alternative or in addition to XML.
As such, JSON offers a few advantages over XML:
</p>
<ul>
<li>less verbosity, resulting in a smaller amount of data to transfer</li>
<li>directly deserializable to yield the corresponding object structure using the javascript built-in <code>eval()</code> function.</li>
</ul>
<p>
Usually, database data is mapped to the JSON format downstream of the database.
For example, PHP offers an extension to convert PHP data structures to JSON.
The disadvantage of that approach is that it usually requires one to iterate through
the resultset to create an object structure out of it before that object structure
can be encoded into the JSON format.
Also, JSON encoding using an UDF is much faster.
</p>
<h2>General notes of usage</h2>
<p>
There are a few things to keep in mind when using these UDF's.
The JSON UDFs are designed for ease of use.
They were designed with the assumption in mind that in many cases,
only a simple and straightforward mapping is required.
So, the UDFs try to make simple mappings easy and with as little overhead as possible.
At the same time the UDFs will still be useful to generate more complicated mappings.
</p>
<p>
Here's a general overview of general functionality offered by <code>lib_mysqludf_json</code>:
</p>
<dl>
<dt>Variable-length parameterlists allow straightforward mapping</dt>
<dd>
In many cases, a mapping is needed to map row-level data
to either a JSON array or a JSON object.
Variable argument lists allow a single method invocation
to accept a whole list of items that are
subsequently mapped to JSON structures.
</dd>
<dt>Proper escaping of string data</dt>
<dd>
String values are mapped to JSON strings.
These strings are automatically escaped to conform to the JSON syntax.
At the same time it is very straightforward
to bypass the automatic escaping behaviour when necessary.
</dd>
<dt>Consistent <code>NULL</code> handling</dt>
<dd>
Database <code>NULL</code> values are handled according to the datatype.
String <code>NULL</code> values map to javascript <code>null</code>,
and numeric <code>NULL</code> values map to javascript <code>NaN</code>.
</dd>
<dt>Automatic mapping of expressions to JSON object members</dt>
<dd>
Often, when creating JSON objects from relational data,
it is perfectly fine to use column names as object member names.
In those cases, one need not provide member names explicitly.
The functions simple reuse the literal text of the argument expressions.
At the same time, it is easy to map members to a custom name.
</dd>
<dt>Basic validity checking of JSON member names</dt>
<dd>
Constant object member names are checked to see if they are valid
JSON member names.
</dd>
<dt>Support for easy nesting of JSON structures</dt>
<dd>
Occassionally, there is a need for nesting JSON objects and/or arrays.
Nesting of JSON UDF functions is intuitive and straigtforward.
Normally, a string result returned by an inner function would be escaped
by the outer function, but this is not the case when nesting the json UDF's.
</dd>
</dl>
<h3>Escaping and quoting of string data</h3>
<p>
Argument string values are normally automatically mapped to JSON double quoted strings.
The string value is escaped to for a valid JSON value.
The following characters are escaped by prefixing them with a <code>\</code> or slash character:
</p>
<ul>
<li><code>"</code> - The double quote character is mapped as <code>\"</code></li>
<li>linefeed - The linefeed character is mapped to <code>\n</code></li>
<li>carriage return - The carriage return character is mapped to <code>\r</code></li>
<li><code>\</code> - The backslash character is mapped to <code>\\</code></li>
</ul>
<p>
Quoting and escaping can be avoided if the expression that returns the string
starts with the prefix <code>json_</code> or has an alias starting with that prefix.
</p>
<h3>Mapping expressions to Object members</h3>
<p>
To make it as easy as possible to map rows to JSON objects, the <code>json_object</code> function
maps each argument to one member.
The expression text is used as member name, and the value as member value.
This allows a lot of common cases to be handled without effort.
</p>
<p>
However, that is not to say that the <code>json_object</code> function blindly uses the expression text.
With a few exceptions, the expressions are checked to see if they make a valid JSON member name.
In some cases, a modification of the expression text is used.
Currently modication occurs for qualified names.
A qualified column expression like <code>aTableAlias.columnName</code> is modified to read <code>columnName</code>.
</p>
<p>
It is always possible to override the automatic member name mapping.
This is done by including an alias to the expression inside the function call.
</p>
<h1>Functions</h1>
<p>
This library <code>lib_mysqludf_json</code> aims to offer a complete set of functions to map relational data into JSON format.
The following functions are currently supported:
</p>
<ol>
<li>
<a href="#json_array"><code>json_array</code></a>
- maps a variable number of arguments to a JSON array.
</li>
<li>
<a href="#json_members"><code>json_members</code></a>
- maps a variable number of name-value pairs to a list of JSON object members.
</li>
<li>
<a href="#json_object"><code>json_object</code></a>
- maps a variable number of arguments to a JSON object.
</li>
<li>
<a href="#json_values"><code>json_values</code></a>
- maps a variable number of arguments to JSON values.
</li>
</ol>
<p>
Use <a href="#lib_mysqludf_json_info"><code>lib_mysqludf_json_info()</code></a> to obtain information about the currently installed version of <code>lib_mysqludf_json</code>.
</p>
<a name="json_array"></a><h2>json_array</h2>
<p>
<code>json_array</code> takes a variable number of arguments and returns a string that denotes a javascript array with the arguments as members.
</p>
<h3>Syntax</h3>
<pre>json_array([<b>arg1</b>,..,<b>argN</b>])</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>[<b>arg1</b>,..,<b>argN</b>]</code></dt>
<dd>
A (possibly empty) list of values of any type.
<ul>
<li>Numeric types are mapped to decimal numbers.</li>
<li>Strings, bit and temporal types are mapped to double quoted strings.</li>
</ul>
<code>NULL</code> values are handled as follows:
<ul>
<li>For numeric types, <code>NULL</code> is mapped to <code>NaN</code>.</li>
<li>For string types, <code>NULL</code> is mapped to <code>null</code>.</li>
</ul>
The argumentlist maybe empty, which case an string is returned that denotes an empty array.
</dd>
<dt>returns</dt>
<dd>
A javascript expression that evaluates to an array.
</dd>
</dl>
<h3>Installation</h3>
<p>
Place the shared library binary in an appropriate location.
Log in to mysql as root or as another user with sufficient privileges, and select any database.
Then, create the function using the following DDL statement:
</p>
<pre>
CREATE FUNCTION json_array RETURNS STRING SONAME 'lib_mysqludf_json.so';
</pre>
<p>
The function will be globally available in all databases.
</p>
<p>
The deinstall the function, run the following statement:
</p>
<pre>
DROP FUNCTION json_array;
</pre>
<h3>Examples</h3>
<p>
Create an array of user data:
</p>
<pre>
select json_array(
customer_id
, first_name
, last_name
, last_update
) as customer
from customer
where customer_id =1;
</pre>
<p>yields this result:</p>
<pre>
+------------------------------------------+
| customer |
+------------------------------------------+
| [1,"MARY","SMITH","2006-02-15 04:57:20"] |
+------------------------------------------+
</pre>
<p>
To obtain nested arrays, simply nest the function calls:
</p>
<pre>
select json_array(
customer_id
, json_array(
first_name
, last_name
)
, last_update
) as customer
from customer
where customer_id =1;
</pre>
<p>yields this result:</p>
<pre>
+--------------------------------------------+
| customer |
+--------------------------------------------+
| [1,["MARY","SMITH"],"2006-02-15 04:57:20"] |
+--------------------------------------------+
</pre>
<p>
Note that arrays can be nested freely as described.
The string returned by the inner <code>json_</code> function is not
escaped by the outer <code>json_</code> function.
</p>
<p>
Note that string values are automatically mapped to double-quoted json strings.
If need be, special characters are escaped.
For example:
</p>
<pre>
select json_array('\"\"');
</pre>
<p>
returns:
</p>
<pre>
+--------------------+
| json_array('\"\"') |
+--------------------+
| ["\"\""] |
+--------------------+
</pre>
<p>
Sometimes one needs to avoid this automatic escaping.
This would be the case for example when storing JSON strings directly in the database.
If you want to avoid escaping to map a string value as is, use an alias with the prefix <code>json_</code>.
</p>
<pre>
select json_array('\"\"' json_);
</pre>
<p>
returns:
</p>
<pre>
+--------------------------+
| json_array('\"\"' json_) |
+--------------------------+
| [""] |
+--------------------------+
</pre>
<p>
The alias is not required when the expression itself already has a name that starts with the <code>json_</code> prefix.
For example, if you have a table with a column called <code>json_data</code>, then the value for that column will not be escaped.
If that is actually an undesired effect, and you realy want to escape the value after all,
simply use an alias with a prefix not equal to <code>json_</code>.
</p>
<h3>See Also</h3>
<ul>
<li><a href="#json_object"><code>json_object</code></a></li>
</ul>
<a name="json_members"></a><h2>json_members</h2>
<p>
This function can be used to turn an arbitrary list
of name-value pairs into a list of JSON object members.
Normally, one does not need this function directy,
as the automatic mapping offered by <code>json_object</code>
is usually sufficient.
</p>
<p>
One of the things the automatic mapping does not handle well
is nesting objects. The <code>json_members</code> function was
designed especially for the purpose of easy nesting of objects,
and also to work with array-type object memebers.
</p>
<h3>Syntax</h3>
<pre>json_members(<b>name1</b>,<b>value1</b>[,..,..,<b>nameN</b>,<b>valueN</b>])</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code><b>name1</b>,<b>value1</b>[,..,..,<b>nameN</b>,<b>valueN</b>]</dt>
<dd>
A list of name-value pairs.
The name is used as a member JSON object member name.
The value is used as the value of the member.
</dd>
<dt>returns</dt>
<dd>A comma separated list of JSON member names with the associated JSON value.</dd>
</dl>
<p>
If the name is a constant expression, a check is performed to see if it yields a valid json member name.
In that case, a modification is applied to qualified column names in that the qualifier is stripped from the member name.
If the name is not a constant expression, no checking is performed at all.
The value of the name argument is simply used as-is as member name, which may lead to syntactically invalid JSON expressions.
</p>
<h3>Installation</h3>
<p>
Place the shared library binary in an appropriate location.
Log in to mysql as root or as another user with sufficient privileges, and select any database.
Then, create the function using the following DDL statement:
</p>
<pre>
CREATE FUNCTION json_members RETURNS STRING SONAME 'lib_mysqludf_json.so';
</pre>
<p>
The function will be globally available in all databases.
</p>
<p>
The deinstall the function, run the following statement:
</p>
<pre>
DROP FUNCTION json_members;
</pre>
<h3>Examples</h3>
<p>
To obtain nested objects, use this function as an argument for <code>json_object</code>
</p>
<pre>
select json_object(
f.last_update
, json_members(
'film'
, json_object(
f.film_id
, f.title
, f.last_update
)
, 'category'
, json_object(
c.category_id
, c.name
, c.last_update
)
)
) as film_category
from film_category fc
inner join film f
on fc.film_id = f.film_id
inner join category c
on fc.category_id = c.category_id
where f.film_id =1;
</pre>
<p>yields a string representing the following JSON object (indentation added for readability):</p>
<pre>
{
last_update:"2006-02-15 05:03:42"
, film:{
"film_id":1
, "title":"ACADEMY DINOSAUR"
, "last_update":"2006-02-15 05:03:42"
}
, category:{
"category_id":6
, "name":"Documentary"
, "last_update":"2006-02-15 04:46:27"
}
}
</pre>
<p>
Note that the result of the inner <code>json_object</code> calls is not escaped.
That's because <code>json_members</code> will not automatically escape values from expressions where the literal expression text has the prefix <code>json_</code>.
This can pose a problem.
If you want to use <code>json_members</code> to map a string in column, no escaping is applied if the column happens to have a name that starts with <code>json_</code>.
In those cases, you can use an alias in the function call:
</p>
<pre>
select json_object(
json_members(
'data'
, json_data data
)
)
from json_data_table
</pre>
<h3>See Also</h3>
<ul>
<li><a href="#json_object"><code>json_object</code></a></li>
</ul>
<a name="json_object"></a><h2>json_object</h2>
<p>
<code>json_values</code> takes a variable number of arguments, maps them to JSON values and returns a concatenation of these.
</p>
<h3>Syntax</h3>
<pre>json_object([<b>arg1</b>,..,<b>argN</b>])</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>[<b>arg1</b>,..,<b>argN</b>]</code></dt>
<dd>
A (possibly empty) list of values of any type.
<p>
The expression text passed as argument is used as member name.
This is by design:
it makes it very easy to render plain table data as json objects.
The expression is checked to verify that it yields a valid javascript identifier.
</p>
<p>
Sometimes, it is not appropriate to use the expression as member name.
In those cases, one can use an alias inside the function call (see examples).
</p>
<p>
The argument value is the value of the corresponing member.
</p>
<ul>
<li>Numeric types are mapped to decimal numbers.</li>
<li>Strings, bit and temporal types are mapped to double quoted strings.</li>
</ul>
<p>
<code>NULL</code> values are handled as follows:
</p>
<ul>
<li>For numeric types, <code>NULL</code> is mapped to <code>NaN</code>.</li>
<li>For string types, <code>NULL</code> is mapped to <code>null</code>.</li>
</ul>
<p>
The argumentlist maybe empty, which case an empty string is returned.
</p>
</dd>
<dt>returns</dt>
<dd>
A javascript expression that evaluates to an javascript value.
</dd>
</dl>
<h3>Installation</h3>
<p>
Place the shared library binary in an appropriate location.
Log in to mysql as root or as another user with sufficient privileges, and select any database.
Then, create the function using the following DDL statement:
</p>
<pre>
CREATE FUNCTION json_object RETURNS STRING SONAME 'lib_mysqludf_json.so';
</pre>
<p>
The function will be globally available in all databases.
</p>
<p>
The deinstall the function, run the following statement:
</p>
<pre>
DROP FUNCTION json_object;
</pre>
<h3>Examples</h3>
<p>
Create an object of user data:
</p>
<pre>
select json_object(
customer_id
, first_name
, last_name
, last_update
) as customer
from customer
where customer_id =1;
</pre>
<p>yields this result:</p>
<pre>
+---------------------------------------------------------------------------------------+
| customer |
+---------------------------------------------------------------------------------------+
| {customer_id:1,first_name:"MARY",last_name:"SMITH",last_update:"2006-02-15 04:57:20"} |
+---------------------------------------------------------------------------------------+
</pre>
<p>
Use the <a href="#json_member"><code>json_member</code></a> function to nest objects:
</p>
<pre>
select json_object(
f.last_update
, json_members(
'film'
, json_object(
f.film_id
, f.title
, f.last_update
)
, 'category'
, json_object(
c.category_id
, c.name
, c.last_update
)
)
) as film_category
from film_category fc
inner join film f
on fc.film_id = f.film_id
inner join category c
on fc.category_id = c.category_id
where f.film_id =1;
</pre>
<h3>See Also</h3>
<ul>
<li><a href="#json_members"><code>json_members</code></a></li>
</ul>
<a name="json_values"></a><h2>json_values</h2>
<p>
<code>json_values</code> takes a variable number of arguments and returns a string that denotes a concatenation of javascript objects
</p>
<h3>Syntax</h3>
<pre>json_values([<b>arg1</b>,..,<b>argN</b>])</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>[<b>arg1</b>,..,<b>argN</b>]</code></dt>
<dd>
A (possibly empty) list of values of any type.
<ul>
<li>Numeric types are mapped to decimal numbers.</li>
<li>Strings, bit and temporal types are mapped to double quoted strings.</li>
</ul>
<p>
<code>NULL</code> values are handled as follows:
</p>
<ul>
<li>For numeric types, <code>NULL</code> is mapped to <code>NaN</code>.</li>
<li>For string types, <code>NULL</code> is mapped to <code>null</code>.</li>
</ul>
<p>
The argumentlist maybe empty, in which case an empty string is returned.
</p>
</dd>
<dt>returns</dt>
<dd>
A javascript value.
</dd>
</dl>
<h3>Examples</h3>
<p>
Create an javascript value:
</p>
<pre>
select json_values('json');
</pre>
Returns:
<pre>
+---------------------+
| json_values('json') |
+---------------------+
| "json" |
+---------------------+
1 row in set (0.00 sec)
</pre>
</body>
</html>
Something went wrong with that request. Please try again.