X

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.

Join the discussion

Comments ( 3 )
  • Federico Razzoli Saturday, March 7, 2015

    Definitely interesting. Do these functions work with 5.6? And MariaDB 10.0?


  • Sveta Smirnova Monday, March 9, 2015

    Thanks!

    They built with 5.6.23, so they definitely work with 5.6 =) I test them with 5.5 and 5.7 also. They should work with Maria, but I never tested. I also heard that people have issues with compiling with Maria: you need to change inlude dir in CMakeLists.txt


  • Olivier Bertrand Tuesday, March 10, 2015

    Frederico,

    Concerning MariaDB 10.0 you may be also interested to look at the JSON table type of the CONNECT storage plugin.


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

Integrated Cloud Applications & Platform Services