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

Thursday Jan 23, 2014

MySQL and PostgreSQL JSON functions: do they differ much?

As author of MySQL JSON functions I am also interested in how development goes in another parties. JSON functions and operators in PostgreSQL, indeed, have great features. Some of them, such as operators, I can not do using UDF functions only. But lets see how these functions are interchangeable.

Note: all PostgreSQL examples were taken from PostgreSQL documentation.

First topic is syntax sugar.

 PostgreSQL
MySQL

Operator ->

postgres=# select '[1,2,3]'::json->2;
 ?column?
----------
 3
(1 row)

postgres=# select '{"a":1,"b":2}'::json->'b';
 ?column?
----------
 2
(1 row)


JSON_EXTRACT

mysql> select json_extract('[1,2,3]', 2);
+----------------------------+
| json_extract('[1,2,3]', 2) |
+----------------------------+
| 3                          |
+----------------------------+
1 row in set (0.00 sec)

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

Operator ->>

postgres=# select '[1,2,3]'::json->>2;
 ?column?
----------
 3
(1 row)

postgres=# select '{"a":1,"b":2}'::json->>'b';
 ?column?
----------
 2
(1 row)


JSON_EXTRACT

mysql> select json_extract('[1,2,3]', 2);
+----------------------------+
| json_extract('[1,2,3]', 2) |
+----------------------------+
| 3                          |
+----------------------------+
1 row in set (0.00 sec)

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

Operator #>

postgres=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}';
 ?column?
----------
 3
(1 row)


JSON_EXTRACT

mysql> select json_extract('{"a":[1,2,3],"b":[4,5,6]}', 'a', 2);
+---------------------------------------------------+
| json_extract('{"a":[1,2,3],"b":[4,5,6]}', 'a', 2) |
+---------------------------------------------------+
| 3                                                 |
+---------------------------------------------------+
1 row in set (0.00 sec)

Operator #>>

postgres=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
 ?column?
----------
 3
(1 row)

JSON_EXTRACT

mysql> select json_extract('{"a":[1,2,3],"b":[4,5,6]}', 'a', 2);
+---------------------------------------------------+
| json_extract('{"a":[1,2,3],"b":[4,5,6]}', 'a', 2) |
+---------------------------------------------------+
| 3                                                 |
+---------------------------------------------------+
1 row in set (0.00 sec)


Then PostgreSQL JSON functions

 PostgreSQL
 MySQL
array_to_json(anyarray [, pretty_bool])  Not supported
row_to_json(record [, pretty_bool])  Not supported
to_json(anyelement)  Not supported
json_array_length(json)  Not supported, planned as bug #70580
json_each(json)  Not supported, cannot be implemented using UDFs only
json_each_text(from_json json)  Not supported, cannot be implemented using UDFs only

json_extract_path(from_json json, VARIADIC path_elems text[])


postgres=# select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4');
  json_extract_path   
----------------------
 {"f5":99,"f6":"foo"}
(1 row)

JSON_EXTRACT

mysql> select json_extract('{"f2":{"f3":1},\c

"f4":{"f5":99,"f6":"foo"}}','f4');
+---------------------------------+
| json_extract('{"f2":{"f3":1},

"f4":{"f5":99,"f6":"foo"}}','f4') |
+---------------------------------+
| {"f5":99,"f6":"foo"}            |
+---------------------------------+
1 row in set (0.00 sec)

json_extract_path_text(from_json json, VARIADIC path_elems text[])


postgres=# select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');
 json_extract_path_text
------------------------
 foo
(1 row)

 JSON_EXTRACT

select json_extract('{"f2":{"f3":1},\c

"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');
+---------------------------------------+
| json_extract('{"f2":{"f3":1},

"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') |
+---------------------------------------+
| foo                                   |
+---------------------------------------+
1 row in set (0.01 sec)

json_object_keys(json)  Not supported, cannot be implemented using UDFs only
json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]  Not supported, cannot be implemented using UDFs only
json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]  Not supported, cannot be implemented using UDFs only
json_array_elements(json)  Not supported, cannot be implemented using UDFs only

And, finally, MySQL functions

 MySQL
 PostgreSQL
json_append  Not supported, but user can convert JSON value into various formats, then create new JSON document from it
json_contains_key  Not supported, however has function json_object_keys(json)
json_extract  Supported in numerous ways, see above
json_merge   Not supported, but user can convert JSON value into various formats, then create new JSON document from it
json_remove   Not supported, but user can convert JSON value into various formats, then create new JSON document from it
 json_replace
  Not supported, but user can convert JSON value into various formats, then create new JSON document from it
json_search  Not supported
json_set   Not supported, but user can convert JSON value into various formats, then create new JSON document from it
json_test_parser   Not supported
json_valid  Not supported, however it has JSON type and checks every element for validity


As a conclusion I can say that MySQL and PostgreSQL implementations mostly intersect in extracting elements while solve element validation and manipulation tasks in different ways.
About

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

Search

Categories
Archives
« January 2014 »
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
24
25
26
27
28
30
31
 
       
Today