Olivier Bertrand added a comment - 2017-05-29 13:43 - edited It seems that these functions have a general problem recognizing the type of their argument (JSON or not JSON?) for instance: select json_array(5,json_array(1,2)); +-------------------------------+ | json_array(5,json_array(1,2)) | +-------------------------------+ | [5, [1, 2]] | +-------------------------------+ Normal, the return from JSON_ARRAY is a JSON item. select json_array(5,'[1,2]'); +-----------------------+ | json_array(5,'[1,2]') | +-----------------------+ | [5, "[1,2]"] | +-----------------------+ This time, the function as regarded its second argument as a string. select json_query('[1,2]','$'); +-------------------------+ | json_query('[1,2]','$') | +-------------------------+ | [1,2] | +-------------------------+   select json_array(5,json_query('[1,2]','$')); +---------------------------------------+ | json_array(5,json_query('[1,2]','$')) | +---------------------------------------+ | [5, "[1,2]"] | +---------------------------------------+ This is wrong. The description of the JSON_QUERY function says Given a JSON document, returns an object or array specified by the path. Returns NULL if not given a valid JSON document, or if there is no match. Here the JSON_QUERY function, unlike the JSON_ARRAY one, has regarded its first argument as a valid JSON item since it did not return NULL. Therefore, according to the function description, it should have returned a JSON item like the JSON_ARRAY function does. But this time, its result has been regarded as a string. All this is not coherent.

Elena Stepanova added a comment - 2017-06-02 17:42 I agree, there is inconsistency here that cannot be justified by "MariaDB doesn't have JSON type" excuse. It wouldn't be surprising if all the above were treating results of nested functions as strings – that could be explained by the absence of JSON type, – but if example 1 works, there is no obvious reason why example 4 does not.

Olivier Bertrand added a comment - 2017-06-02 21:14 In addition, there is more problems when the argument is the result of a UDF function: select json_extract(json_object('truc',json_array('machin',4,json_object('chose','XML'))), jsonlocate(json_object('truc',json_array('machin',4,json_object('chose','XML'))),'XML')); This simply ... make the server crash. Jsonlocate is a JSON UDF that here returns the string: $.truc[2].chose Given that string as argument the function json_extract works fine.

Alexey Botchkov added a comment - 2017-09-12 14:09 - edited What is in the 'Description' part is not a bug. (and yes, MySQL returns just same result. Explanation is simple - the json_search returns JSON, and the json_extract expects a string. You can fix that using json_unquote(). MariaDB [(none)]> select json_extract(json_object('truc',json_array('machin',4,json_object('chose','XML'))),json_unquote(json_search(json_object('truc',json_array('machin',4,json_object('chose','XML'))),'one','XML'))) as Result; +--------+ | Result | +--------+ | "XML" | +--------+ 1 row in set (0.00 sec)

Alexey Botchkov added a comment - 2017-09-12 14:23 - edited select json_array(5,'[1,2]'); +-----------------------+ | json_array(5,'[1,2]') | +-----------------------+ | [5, "[1,2]"] | +-----------------------+ This time, the function as regarded its second argument as a string. This is also the correct result. Both MariaDB and MySQL return this. You need to specify that the second parameter is JSON explicitly. MariaDB [(none)]> select json_array(5,json_compact('[1,2]')); +-------------------------------------+ | json_array(5,json_compact('[1,2]')) | +-------------------------------------+ | [5, [1,2]] | +-------------------------------------+ 1 row in set (0.00 sec)

Alexey Botchkov added a comment - 2017-09-12 14:43 select json_array(5,json_query('[1,2]','$')); +---------------------------------------+ | json_array(5,json_query('[1,2]','$')) | +---------------------------------------+ | [5, "[1,2]"] | +---------------------------------------+ This is indeed wrong. FIxed.

Alexey Botchkov added a comment - 2017-09-12 21:03 Crashing UDF call also fixed. Though for me that jsonlocate call returned ' truc: [2] :chose ' which was not a right path.

Olivier Bertrand added a comment - 2017-09-13 09:30 - edited Alexey: you probably used a deprecated version of this UDF. Current version returns '$.truc [2] .chose'. Thanks for fixing it anyway.

Ian Gilfillan added a comment - 2017-09-13 10:26 MySQL and MariaDB don't behave quite the same in that MariaDB gives a warning, MySQL an error - I've reported this separately as MDEV-13794