Jan
23
2014
MySQL and PostgreSQL JSON functions: do they differ much?
Posted by Sveta Smirnova on Thu 23 Jan 2014 19:54 UTC
Tags:

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.