Saturday Mar 07, 2015

JSON UDF functions version 0.4.0 have been released

New version of JSON UDF functions has been just released. This version introduces two new features.

  1. Escape characters in results of JSON_SEARCH function. In previous version if you had document @doc = '{"foo:bar": "baz"}' or '{"foo": {"bar": "baz"}}' return value of JSON_SEARCH(@doc, '"baz"'); was 'foo:bar::' for both. There was no way to distinguish two search paths. Now for the first document JSON_SEARCH returns 'foo\:bar::'
  2. Second feature is much bigger. Now JSON functions accessing elements by a key (JSON_CONTAINS_KEY, JSON_EXTRACT, JSON_REPLACE, JSON_REMOVE, JSON_APPEND, JSON_SET, JSON_COUNT) can use alternate keypath syntax: $.key1.key2[arr_index] where $ is root element, .key is key name, [arr_index] is array index. JSON_SEARCH can also return path in this format with escaped $, . and [] symbols.

Let's see how it works.

mysql> set @doc='{"JSON UDFs": {"version": "0.4.0", "functions": ["json_extract", "json_contains_key", "json_search"]}}';
Query OK, 0 rows affected (0.00 sec)

mysql> select JSON_EXTRACT(@doc, '$.JSON UDFs.version');
+-------------------------------------------+
| JSON_EXTRACT(@doc, '$.JSON UDFs.version') |
+-------------------------------------------+
| 0.4.0                                     |
+-------------------------------------------+
1 row in set (0.00 sec)

Accessing array element:


mysql> select JSON_EXTRACT(@doc, '$.JSON UDFs.functions[1]');
+------------------------------------------------+
| JSON_EXTRACT(@doc, '$.JSON UDFs.functions[1]') |
+------------------------------------------------+
| json_contains_key                              |
+------------------------------------------------+
1 row in set (0.00 sec)

Searching:

mysql> select JSON_SEARCH(@doc, '"json_search"');
+------------------------------------+
| JSON_SEARCH(@doc, '"json_search"') |
+------------------------------------+
| $.JSON UDFs.functions[2]           |
+------------------------------------+
1 row in set (0.01 sec)

Now you can use result of JSON_SEARCH as an argument for other functions:

mysql> select JSON_EXTRACT(@doc, JSON_SEARCH(@doc, '"json%"'));
+--------------------------------------------------+
| JSON_EXTRACT(@doc, JSON_SEARCH(@doc, '"json%"')) |
+--------------------------------------------------+
| json_extract                                     |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> select JSON_EXTRACT(@doc, JSON_SEARCH(@doc, '"json%"', 2));
+-----------------------------------------------------+
| JSON_EXTRACT(@doc, JSON_SEARCH(@doc, '"json%"', 2)) |
+-----------------------------------------------------+
| json_contains_key                                   |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Old style of keypath is also supported, but two styles cannot work simultaneously. To use old path style you need to install libmy_json_udf library and to use new path style - libmy_json_udf_path. There is also install_jsonudf_path.sql script installing functions which support new path style automatically.

As usual functions are available at MySQL Labs.

Thursday Jan 15, 2015

JSON UDF functions 0.3.3 have been released

New version of JSON UDF functions has been just released. This is maintenance release which only contains fix for 4 bugs:

74090/19821398 `INSTALL_JSONUDFS` ISSUES A WARNING DUE TO TRUNCATION OF `VERSION_COMPILE_OS`
74091/19692634 `JSON_DEEP_MERGE`: DEEP CRASH IN `MERGE_TREE`
74092/19692628 CRASH WITH SECOND-HAND NULL IN PARAMETERS
74097/19693198 `CREATE_NEW_VALUE`: STACK BUFFER OVERFLOW, %LLD CAN'T FIT IN CHAR[20]

Also 0.3 branch becomes stable branch and no new features will be pushed to this branch in future. Previous stable branch (0.2) removed from MySQL Labs website.

As usual you can download functions from http://labs.mysql.com/.

Thursday Sep 25, 2014

JSON UDF functions version 0.3.2 have been released

Today new version of JSON UDF functions: 0.3.2 was released. This is development release which contains new functionality. You can download functions from the MySQL Labs website.

What is new?

New function `JSON_COUNT` added. This function returns number of children of the key path specified. If no key path specified, number of children of the root element is returned. Bug #70580/17584692 ADD FUNCTION `JSON_COUNT` TO COUNT SIZE OF JSON DOCUMENT

mysql> select json_count('{"MySQL Central": ["conference", 2014]}') as 'root count',
    ­> json_count('{"MySQL Central": ["conference", 2014]}', 'MySQL Central') as 'first element count'\G     
                                                   
************************ 1. row ************************
         root count: 1
first element count: 2
1 row in set (0.02 sec)

New function `JSON_VERSION`, returning version of JSON UDFs, added. Bug #72197/18499329 `JSON_VERSION` WOULD BE USEFUL

mysql> select json_version();
+­­­­­­­­­­­­­­­­­­­­­­­­­­­­----------------------------+
| json_version()             |
+­­­­­­­­­­­­­­­­­­­­­­­­­­­­----------------------------+
| MySQL JSON UDFs 0.3.2­labs  |
+­­­­­­­­­­­­­­­­­­­­­­­­­­­­----------------------------+
1 row in set (0.00 sec)

`JSON_SEARCH` now accepts wildcards. This also means if you want exact match of '\_' or '%' characters, you need to escape them. Bug #70571/17583417 ADD POSSIBILITY TO SEARCH IN LIKE-MANNER FOR `JSON_SEARCH`

mysql> set @doc='{"people": [{"name1": "Joe"}, {"name2": "John"}, {"name3": "Jon"}]}';
Query OK, 0 rows affected (0.00 sec)
mysql> select json_search(@doc, '"Jo_"', 1);
+-------------------------------+
| json_search(@doc, '"Jo_"', 1) |
+-------------------------------+
| name1:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo_"', 2);
+-------------------------------+
| json_search(@doc, '"Jo_"', 2) |
+-------------------------------+
| name3:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo_"', 3);
+-------------------------------+
| json_search(@doc, '"Jo_"', 3) |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo%"', 1);
+-------------------------------+
| json_search(@doc, '"Jo%"', 1) |
+-------------------------------+
| name1:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo%"', 2);
+-------------------------------+
| json_search(@doc, '"Jo%"', 2) |
+-------------------------------+
| name2:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@doc, '"Jo%"', 3);
+-------------------------------+
| json_search(@doc, '"Jo%"', 3) |
+-------------------------------+
| name3:0:people::              |
+-------------------------------+
1 row in set (0.00 sec)

Added logging abilities to JSON functions. Due to limitation of UDF interface it is not possible to return meaningful error message if error happened after initial arguments check. But having information why a function fails for one or another reason can be useful for troubleshooting. Therefore added new CMake option `VERBOSE_LEVEL` which can take values quiet, note, warning and error. Default mode is quiet: no additional error output. If `VERBOSE_LEVEL` set to either note, warning or error, all messages of the level and up will be written into server error log file. For users meaningful value is only error. Values note and warning are used for development only. Bug #71951/18391372 ADD LOGGING POSSIBILITIES TO JSON FUNCTIONS Note, you have to build the functions yourself if want to use functions.

To show example of this functionality lets create invalid document, then call function JSON_VALID and examine log file:

mysql> set @doc='{"people": [{"name1": "Joe"}, {"name2": "John"}, {"name3": "Jon"}}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_valid(@doc);
+------------------+
| json_valid(@doc) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

[sveta@delly mysql-test]$ cat var/log/mysqld.1.err 
...
2014-09-24 20:09:19 10845 [Note] Event Scheduler: Loaded 0 events
2014-09-24 20:09:19 10845 [Note] /home/sveta/src/mysql-5.6/sql/mysqld: ready for connections.
Version: '5.6.21-debug-log'  socket: '/home/sveta/src/mysql-5.6/mysql-test/var/tmp/mysqld.1.sock'  port: 13000  Source distribution
Parse error: document proceeded up to 65'th character }

Error messages are added not to all functions. If you miss error for some example, create a feature request and I will add.

New function `JSON_DEEP_MERGE` added. This function works as `JSON_SAFE_MERGE`, but updates values in case if following document contains duplicate keys Bug #71809/18315925 WHEN USING `JSON_MERGE` IT DUPLICATES KEY'S INSTEAD OF UPDATING THE NEW VALUE Good example of usage is at the bug report page.

Now JSON functions fully support negative keys. So, -1 means last element, -2 last before last and so on. Bug #71230/18315920 IMPLEMENT NEGATIVE INDEXES FOR JSON FUNCTIONS I wrote about supported format at "Last element for JSON array: what do you think?" Bug report contains example of usage too.

Output format for floating point values with exponent changed: Bug #73586/19447246 `JSON_SET` DIES WITH LARGE DECIMAL PARAMETER Earlier numbers, such as 1e2, were converted to decimal format, and not they are printed as 1.000000e+02 if used in functions, such as JSON_REPLACE:

mysql> set @json = '{"a":1,"b":2}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_replace(@json, 'a', 1e2);
+-------------------------------+
| json_replace(@json, 'a', 1e2) |
+-------------------------------+
| {"a":1.000000e+02,"b":2}      |
+-------------------------------+
1 row in set (0.00 sec)

There is also a change in binary packages which we provide. They are for Mac OS X 10.9 and 64-bit only.

Bugs fixed:

72196/18499334 `JSON_APPEND`: ERROR MESSAGE NOT ENTIRELY CORRECT
72962/18960413 `JSON_COUNT` CRASHES SERVER
73486/19383795 `JSON_SET`, `JSON_APPEND` DO NOT APPEND TO EMPTY DOCUMENT
73588/19447238 JSON FUNCTIONS CRASHES IN MEMMOVE CALLED FROM `MAKE_JEARGS`
73587/19447278 `JSON_SET`: UNINTIALIZED VALUE IN JPARSER::`PARSE_NUM` WITH MALFORMED NUMBER
73585/19447265 `JSON_TEST_PARSER` MISBEHAVES...

And yes, I am speaking at OOW about the functions. Attend session "Second Step to the NoSQL Side: MySQL JSON Functions" [CON2494] at Thursday, Oct 2, 12:00 PM

Thursday Mar 27, 2014

JSON UDF functions version 0.3.1 have been released.

Today new version of JSON UDF functions: 0.3.1 was released. This is development release which contains new functionality. You can download functions from the MySQL Labs website.

What is new?

Default value for compile option `WITH_PCRE` now is `bundled` independently from the
platform (Bug #71265/18081332 Library name mismatch for PCRE on Ubuntu)

New values, passed to functions `JSON_APPEND`, `JSON_REPLACE`, `JSON_SET` as numbers, empty strings or `NULL`s are converted to JSON objects. Objects, passed as strings are not converted: you are still responsible to put quotes around string values. Also these functions check if element which needs to be inserted is valid JSON value. (Bug #70394/17491708 MySQL JSON UDFs: json_replace can generate invalid target JSON)

README and ChangeLog files use Markdown format (Bug #71261/18023002 REFERENCES IN README MISSING)

`JSON_SEARCH` now accepts third argument: occurrence number, allowing to specify number of occurrence needed. (Bug #70607/17597479 ADD POSSIBILITY TO SEARCH ALL OCCURRENCES WITH JSON_SEARCH)

Added function `JSON_DEPTH` which returns depth of the JSON document.

Bugs fixed:


71510/18154696 `JSON_EXTRACT` returns wrong value if extracts from object which holds two arrays

71530/18160842 `JSON_EXTRACT` RETURNS WRONG RESULT IN VERSION 0.3

71949/18355404 `JSON_VALID` DOES NOT REJECT INVALID DOCUMENTS

70606/17596818 Implement SAFE versions for all JSON functions

And, of course, this release contains all changes made in version 0.2.2

Wednesday Mar 19, 2014

Why is important to be active at bugs.mysql.com?

When I presented JSON UDF functions at MySQL Connect last year attendees asked me to implement few features. I quickly wrote their wishes in my notepad. I also created feature requests at bugs.mysql.com  when I was home.

During following months I fixed bugs in the functions and implemented feature requests, including those which I got from MySQL Connect. I started from most important and affecting users, such as wrong results bugs of features for which I got more than one request.

But today all such requests finished and I am working on bug #70580  This feature request says: "Add function JSON_COUNT which will go through the JSON document and count number of childs of the root element." Well, I wrote it myself, but I don't exactly remember if the user wanted to know depth of the JSON document, e.g. (Scenario 1) if for document {"a": {"b": "c", "d": "e", "f": "g"}} result should be 3 and for document {"a": "b", "c": "d", "e": "f", "g": "h"} result should be 2.

Or he wanted to know how many element has the whole document? (Scenario 2) In this case for document {"a": {"b": "c", "d": "e", "f": "g"}} result should be 5 and for document {"a": "b", "c": "d", "e": "f", "g": "h"} result should be 5.

Or he wanted exactly what I wrote in the bug report: how many children root element has? (Scenario 3) In this case for document {"a": {"b": "c", "d": "e", "f": "g"}} result should be 1 and for document {"a": "b", "c": "d", "e": "f", "g": "h"} result should be 4.

If he'd file bug report himself, I'd ask. But he did not and left no comment for this bug report.

What will I do next? I will implement function JSON_DEPTH which will work exactly as described in the Scenario 1 and which, if I remember correctly, is what the user originally wanted and leave this feature request open for comments if somebody needs behavior as described in Scenario 2 or Scenario 3.

Wednesday Jan 29, 2014

JSON UDF functions version 0.2.2 have been released.

New version of JSON UDF functions 0.2.2 have been just released. It is last maintenance release of 0.2 series. However it contains two new features:

JSON_VALID now accepts array as a root element of the JSON document (Bug#70567/17583282)

JSON functions can now be installed and uninstalled using a script (Bug#71263/18022788 Easy installation sql script).

This feature is Community contribution. Thank you, Daniel van Eeden!

This release also contains following bug fixes:

71050/17882710 json_extract returning same column twice if key names are not fully distinct. (Fixed for JSON_EXTRACT, JSON_CONTAINS_KEY, JSON_APPEND, JSON_REMOVE, JSON_REPLACE, JSON_SET)

71180/17975709 JSON_CONTAINS_KEY founds not existent key in the array element

71267/18020724 buffer underrun in JParser::parse_pair

71264/18022761 No uninstall information in the README

71261/18023002
References in README missing

Tuesday Dec 31, 2013

JSON UDF functions version 0.3.0 have been released.

Today new version of JSON UDF functions: 0.3.0 was released. This is major release which contains new functionality. You can download functions from the MySQL Labs website.

What was added?

Functions JSON_EXTRACT, JSON_CONTAINS_KEY, JSON_APPEND, JSON_REMOVE, JSON_REPLACE, JSON_SET now accept both string and integer arguments as array indexes. (Bug #70393/17491709)

Now syntax JSON_EXTRACT(doc, 'key', 1); is accepted.

New function JSON_SAFE_MERGE checks JSON documents for validity before merging them. In addition JSON_MERGE now merges valid documents, having array as root element. (Bug #70575/17583568)

JSON_VALID now accepts array as a root element of the JSON document (Bug #70567/17583282)

Also this release contains following bug fixes:

71050/17882710 json_extract returning same column twice if key names are not fully distinct. (Fixed for JSON_EXTRACT, JSON_CONTAINS_KEY, JSON_APPEND, JSON_REMOVE, JSON_REPLACE, JSON_SET)

71180/17975709 JSON_CONTAINS_KEY founds not existent key in the array element

70579/17584678 Add ability to append last element to array in JSON_APPEND

And, of course, this release contains all changes which version 0.2.1 has

Thursday Oct 10, 2013

JSON UDFs first feedback

Yesterday Ulf Wendel created great blog post with deep analysis of JSON UDF functions which I presented at MySQL Connect at September, 21.

Ulf found few bugs in these functions, I reported them all at bugs.mysql.com. You can find numbers in my comment to his blog post. But he also raised concerns, which can not be considered pure bugs, rather feature requests, or even design especiallities.

* First concern, of course, is the documentation. Ulf writes: "Here’s what the README, the only documentation available apart from the *.c[omment]/*.h[elp] files".

I agree single README file is not enough, but this is still Labs project for which I can not abuse MySQL documentation team for making proper documentation for me. But you still can find more information, than single README file. And these are slides from MySQL Connect, which are available on the conference website (they published them today!) at https://oracleus.activeevents.com/2013/connect/fileDownload/session/470E8AA1C46CAA3A9ACFBDB7653FC313/CON3130_Smirnova.pdf as well as on my own website at http://www.microbecal.com/ftp_load/JSON_UDFs.odp or http://www.microbecal.com/ftp_load/JSON_UDFs.pdf I strongly recommend you to download these slides, because this is the best user manual for JSON functions which exists so far.

But I also want to hear your suggestions about where I can put the complete user manual. I was going to post updates to my blog and update README file. Is this sufficient? Or do you think would be better if I put the documentation at my own website? Or pack it as a separate *-doc package at labs.mysql.com? Any other ideas? If you also miss the documentation, please comment.

* Another request which I have for you is about parameter syntax. Ulf writes: "A function that works “by key” on a subset of a JSON document, usually has the prototype: JSON_SOMETHING(string json_document, string key [, string key...], other_parameter). The first argument passed to the functions is always the JSON document to work on. It follows a variable number of arguments that describe on which subpart of the entire document the function shall work. Having this string key [, string key...] arguments in the middle of the function signature is a bit odd. Some programming languages may forbid this for style considerations. However, depending on the function, further arguments may follow, such as the value to search for or to add."

We discussed this syntax internally before I started implementing the functions and get to this style. But I understand what some people could prefer different syntax. If you such a person, please, file a feature request. At this stage it is easy to rewrite functions, so they support better style of user arguments. I will think some time about it and will post a poll to my blog with proposals which I think can be better than current syntax. Also it is possible to have different set of functions with the same functionality, but which support different style of arguments. Only requirement for this feature is users suggestions and votes. Therefore, please suggest and vote!

* Another feature for which I need your suggestions is "speed vs validity checks". I again will cite Ulf: "Taken from the README. For most functions manipulating or generating JSON documents is true: Warning! This version does not check whole document for validity. Hey, it is a labs.mysql.com pre-production release :-)."

But this design was done not because these functions are at the Labs and not because this is alpha version. We discussed this functionality internally, but decided to don't implement validity checks to speed up functions. If you think we did wrong, please, open a feature request at bugs.mysql.com about *safe* version of the functions. I also think that having two sets of "safe" and "fast" functions can also work. But I need to hear your opinions before implementing this feature.

* Another feature for which I want to count user votes is search abilities. Currently, function JSON_SEARCH uses only exact match. There is no support for wildcards, case insensitive search or full-text search. I got complains about such a limitation not only from Ulf, but from users at MySQL Connect too. Therefore I created two feature requests: http://bugs.mysql.com/bug.php?id=70571 about LIKE and http://bugs.mysql.com/bug.php?id=70572  about FULLTEXT. If you need these features, please go and vote for them using "Affects Me!" Button.

Current behavior of JSON_SEARCH is tricky and Ulf, expectedly, did mistake when tried to use it. Correct syntax should be select json_search('{"key":"value"}', '"value"' ); and select json_search('{"a":{"b":"c"}}', '"c"' ) instead of select json_search('{"key":"value"}', "value" ); and select json_search('{"a":{"b":"c"}}', "c" ) which he used:

mysql> select json_search('{"key":"value"}', '"value"' )\G
*************************** 1. row ***************************
json_search('{"key":"value"}', '"value"' ): key::
1 row in set (0.00 sec)

mysql> select json_search('{"a":{"b":"c"}}', '"c"' );
+----------------------------------------+
| json_search('{"a":{"b":"c"}}', '"c"' ) |
+----------------------------------------+
| b:a::                                  |
+----------------------------------------+
1 row in set (0.00 sec)


This syntax is used to let you search for objects:

mysql> select json_search('{"a":{"b":"c"}}', '{"b":"c"}' );
+----------------------------------------------+
| json_search('{"a":{"b":"c"}}', '{"b":"c"}' ) |
+----------------------------------------------+
| a::                                          |
+----------------------------------------------+
1 row in set (0.00 sec)


You can also notice "::" in the end of the key path. This is the root element and done by design.

And last Ulf's concern is about syntactic sugar:"With a bit of syntactic sugar, say “SELECT document.member[offset]” this could look much more appealing. On the contrary, I am talking syntaxtic sugar only! Syntactic sugar is really hard to add with todays MySQL."

I so agree here! I wish I could implement the functions in such a way! But it is not possible to change MySQL syntax with UDFs!

And while we can not do much for syntactic sugar, you still can help to make better other features. Please comment and vote for the user manual format and location; parameter syntax; safe versions of the functions and extended search capabilites.

Thank you! :)

About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« May 2015
SunMonTueWedThuFriSat
     
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
      
Today