Bug #70579 Add ability to append last element to array in JSON_APPEND
Submitted: 9 Oct 2013 23:15 Modified: 29 Dec 2013 10:31
Reporter: Sveta Smirnova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON User-defined function ( UDF ) Severity:S4 (Feature request)
Version:0.2 OS:Any
Assigned to:
Triage: Needs Triage: D5 (Feature request) / R3 (Medium) / E3 (Medium)

[9 Oct 2013 23:15] Sveta Smirnova
Description:
Currently you have to specify array number to append it to JSON document using JSON_APPEND function. But what if you don't know number of elements? In this case would be good to have ability to append as last element to the array.

How to repeat:
Current behavior:

mysql> select json_append('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '2', '”San Francisco”') as 'MySQL connect';
+--------------------------------------------------------------+
| MySQL connect                                                |
+--------------------------------------------------------------+
| {"MySQL connect": ["conference", 2013, ”San Francisco”]}     |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

Workaround - specify big enough number:

mysql> select json_append('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '22', '”San Francisco”') as 'MySQL connect';
+--------------------------------------------------------------+
| MySQL connect                                                |
+--------------------------------------------------------------+
| {"MySQL connect": ["conference", 2013, ”San Francisco”]}     |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

Suggested fix:
mysql> select json_append('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', LAST_ELEMENT, '”San Francisco”') as 'MySQL connect';
+--------------------------------------------------------------+
| MySQL connect                                                |
+--------------------------------------------------------------+
| {"MySQL connect": ["conference", 2013, ”San Francisco”]}     |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

or

mysql> select json_append('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', NULL, '”San Francisco”') as 'MySQL connect';
+--------------------------------------------------------------+
| MySQL connect                                                |
+--------------------------------------------------------------+
| {"MySQL connect": ["conference", 2013, ”San Francisco”]}     |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

or

mysql> select json_append_last('{"MySQL connect": ["conference", 2013]}', 'MySQL connect', '”San Francisco”') as 'MySQL connect';
+--------------------------------------------------------------+
| MySQL connect                                                |
+--------------------------------------------------------------+
| {"MySQL connect": ["conference", 2013, ”San Francisco”]}     |
+--------------------------------------------------------------+
1 row in set (0.01 sec)
[4 Dec 2013 12:56] Sveta Smirnova
See also https://blogs.oracle.com/svetasmirnova/entry/last_element_for_json_array for design ideas.
[29 Dec 2013 10:30] Sveta Smirnova
Posted by developer:
 
For last element in last array fixed in version 0.3.0

You should use -1 to specify such an element:

set @doc = '{"foo": [{"foo": "bar"}, "baz"]}';

select json_append(@doc, 'foo', -1, '"foo"');
json_append(@doc, 'foo', -1, '"foo"')
{"foo": [{"foo": "bar"}, "baz", "foo"]}

select json_set(@doc, 'foo', -1, '"foo"');
json_set(@doc, 'foo', -1, '"foo"')
{"foo": [{"foo": "bar"}, "baz", "foo"]}

Note difference how string key treated:

select json_set(@doc, 'foo', '-1', '"foo"');
json_set(@doc, 'foo', '-1', '"foo"')
{"foo": [{"foo": "bar"}, "baz", "foo"]}

select json_set(@doc, '-1', '"foo"');
json_set(@doc, '-1', '"foo"')
{"foo": [{"foo": "bar"}, "baz"], "-1": "foo"}

select json_append(@doc, 'foo', '-1', '"foo"');
json_append(@doc, 'foo', '-1', '"foo"')
{"foo": [{"foo": "bar"}, "baz", "foo"]}

There is also separate feature request about true negative indexes support: http://bugs.mysql.com/bug.php?id=71230