-
Notifications
You must be signed in to change notification settings - Fork 55
/
lib_mysqludf_json.html
580 lines (580 loc) · 20.1 KB
/
lib_mysqludf_json.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
<?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>