Bug #70393 MySQL JSON UDFs: json_extract only accepts string arguments
Submitted: 22 Sep 2013 0:02 Modified: 5 Dec 2013 0:30
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON User-defined function ( UDF ) Severity:S3 (Non-critical)
Version:0.2.0 OS:Any
Assigned to: Sveta Smirnova CPU Architecture:Any
Tags: mysql-json-udfs-0.2.0

[22 Sep 2013 0:02] Roland Bouman
Description:
json_extract only accepts string arguments. 

This is unexpected and undesired. 

How to repeat:
set @json = '{
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry": {
          "ID": "SGML",
          "SortAs": "SGML",
          "GlossTerm": "Standard Generalized Markup Language",
          "Acronym": "SGML",
          "Abbrev": "ISO 8879:1986",
          "GlossDef": {
            "para": "A meta-markup language, used to create markup languages such as DocBook.",
            "GlossSeeAlso": ["GML", "XML"]
          },
          "GlossSee": "markup"
        }
      }
    }
  }
}';

mysql> select json_extract(@json, "glossary", "GlossDiv", "GlossList", "GlossEntry", "GlossDef", "GlossSeeAlso", 1);
ERROR 1123 (HY000): Can't initialize function 'json_extract'; JSON_EXTRACT accepts only string arguments

Suggested fix:
If the document contains a javascript array, it should be possible to extract or specify an element of that array using the integer index. This is what one would do in javascript, and integer indices are the common idiom to select from an array in virtually every programming language. 

(Note that if the element in a javascript array is specifid using a string argument, then it should still work as it does now)
[22 Sep 2013 5:29] MySQL Verification Team
Hello Roland,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[22 Sep 2013 5:29] MySQL Verification Team
// How to repeat

// Download udf's from http://labs.mysql.com/, category: MySQL JSON UDFs, package mysql-json-udfs-0.2.0-labs-json-udfs-linux-glibc2.5-x86_64.tar.gz

// Install (on Linux)

Copy libmy_json.so(will have to rename as current packages contain libmy_json_udf.so) into plugin directory of your MySQL server, then login and type:

create function json_valid returns integer soname 'libmy_json.so';
create function json_search returns string soname 'libmy_json.so';
create function json_extract returns string soname 'libmy_json.so';
create function json_replace returns string soname 'libmy_json.so';
create function json_append returns string soname 'libmy_json.so';
create function json_remove returns string soname 'libmy_json.so';
create function json_set returns string soname 'libmy_json.so';
create function json_merge returns string soname 'libmy_json.so';
create function json_contains_key returns integer soname 'libmy_json.so';

// confirm udf's exists

mysql> select * from mysql.func;
+-------------------+-----+---------------+----------+
| name              | ret | dl            | type     |
+-------------------+-----+---------------+----------+
| json_valid        |   2 | libmy_json.so | function |
| json_search       |   0 | libmy_json.so | function |
| json_extract      |   0 | libmy_json.so | function |
| json_replace      |   0 | libmy_json.so | function |
| json_append       |   0 | libmy_json.so | function |
| json_remove       |   0 | libmy_json.so | function |
| json_set          |   0 | libmy_json.so | function |
| json_merge        |   0 | libmy_json.so | function |
| json_contains_key |   2 | libmy_json.so | function |
+-------------------+-----+---------------+----------+
9 rows in set (0.00 sec)

// Use the queries provided by reporter
[5 Dec 2013 0:29] Sveta Smirnova
Posted by developer:
 
Fixed in version 0.3.0.

Now functions json_extract, json_contains_key, json_append, json_remove, json_replace, json_set accept both string and integer arguments as array indexes.
[29 Jan 2014 23:20] a a
Can't extract deeper after first element in array in version 0.3

set @json = '[{"a":1,"b":2}, {"a":3,"b":4}]';
select CAST(json_extract(@json, 0, 'a') AS CHAR(10000));

returns: 1

set @json = '[{"a":1,"b":2}, {"a":3,"b":4}]';
select CAST(json_extract(@json, 1, 'a') AS CHAR(10000));

returns: NULL
expected: 3
[30 Jan 2014 16:40] Sveta Smirnova
Thank you for the feedback.

This is absolutely new issue. I opened new bug report for it: bug #71530 Please subscribe to that report to receive timely update on progress.