X
  • MySQL |
    September 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

Join the discussion

Comments ( 2 )
  • guest Wednesday, February 25, 2015

    Cool. How many more years of half-assed UDF "development" releases so you can keep pretending mysql has JSON functionallity?


  • Sveta Smirnova Thursday, February 26, 2015

    I think you don't understand purpose of this project. This project is not to pretend that MySQL has *built-in* JSON support, but to provide necessary functionality for users who need JSON functions *now* and in *any* MySQL version.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content

Oracle

Integrated Cloud Applications & Platform Services