| 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: | |
| Category: | MySQL Server: JSON User-defined function ( UDF ) | Severity: | S4 (Feature request) |
| Version: | 0.2 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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
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)