JSON_EXTRACT comparison/casting issue

[kevinmartin]

Using MySQL 5.7, it seems like calling JSON_EXTRACT(field, path+) for an integer from a json array isn't allowing me to compare with an integer.

Query:

SELECT *
FROM table
WHERE JSON_EXTRACT(json, "$[2]") >= ?;

The value in the json field is just a simple [1,2,3,4,5].

I try executing the query with both a string and integer value. Both result in now results.

If I insert the query into MySQL Workbench, it works fine. If I screw the prepared statement route and just put the integer in the query in place of the ?, it works fine.

They way around it so far is casting the JSON_EXTRACT value into UNSIGNED for the comparison to work:

SELECT *
FROM table
WHERE CAST(JSON_EXTRACT(json, "$[2]") AS UNSIGNED) >= ?;

[sidorares]

could you post schema creation code for table? ( or just show create sql)?

[sidorares]

also, do are you using query() or execute() workbench is probably not using prepared statements

[kevinmartin]

I'm using execute().

CREATE TABLE `table` (
  `json` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[sidorares]

cold you also help me to populate table? Can you post simple insert queries to match your data?

[kevinmartin]

INSERT INTO `table` (`json`) VALUES ('[1,2,3,4,5]');

[kevinmartin]

The value is always an array of integers.

[sidorares]

works for me:

conn.query('SELECT * FROM tttable WHERE JSON_EXTRACT(json, "$[1]") >= ?', [5], (err, rows) => {
  console.log(err, rows);
});

prints null [ TextRow { json: [ 100, 100, 3, 4, 5 ] } ]

mysql> select * from tttable;
+---------------------+
| json                |
+---------------------+
| [1, 2, 3, 4, 5]     |
| [100, 100, 3, 4, 5] |
+---------------------+
2 rows in set (0.00 sec)

[kevinmartin]

Weird. Any other info I can provide you? Doesn't work for me.

[sidorares]

I'm using mysql server version 5.7.10. Do you have issues when doing same queries from command line?

mysql> select * from tttable;
+---------------------+
| json                |
+---------------------+
| [1, 2, 3, 4, 5]     |
| [100, 100, 3, 4, 5] |
+---------------------+
2 rows in set (0.14 sec)

mysql> select * from tttable WHERE JSON_EXTRACT(json, "$[1]") >= 2;
+---------------------+
| json                |
+---------------------+
| [1, 2, 3, 4, 5]     |
| [100, 100, 3, 4, 5] |
+---------------------+
2 rows in set (0.01 sec)

mysql> select * from tttable WHERE JSON_EXTRACT(json, "$[1]") >= 3;
+---------------------+
| json                |
+---------------------+
| [100, 100, 3, 4, 5] |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from tttable WHERE JSON_EXTRACT(json, "$[1]") < 20;
+-----------------+
| json            |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

[kevinmartin]

Works perfectly in the command line.

[kevinmartin]

CLI seems to be fine:

mysql> SELECT 1, JSON_ARRAY(1), JSON_EXTRACT(JSON_ARRAY(1), "$[0]"), JSON_TYPE(JSON_EXTRACT(JSON_ARRAY(1), "$[0]"));
+---+---------------+-------------------------------------+------------------------------------------------+
| 1 | JSON_ARRAY(?) | JSON_EXTRACT(JSON_ARRAY(?), "$[0]") | JSON_TYPE(JSON_EXTRACT(JSON_ARRAY(?), "$[0]")) |
+---+---------------+-------------------------------------+------------------------------------------------+
| 1 | [1]           | 1                                   | INTEGER                                        |
+---+---------------+-------------------------------------+------------------------------------------------+
1 row in set (0.14 sec)

mysql> PREPARE getJsonType FROM 'SELECT ?, JSON_ARRAY(?), JSON_EXTRACT(JSON_ARRAY(?), "$[0]"), JSON_TYPE(JSON_EXTRACT(JSON_ARRAY(?), "$[0]"));';
Query OK, 0 rows affected (0.15 sec)
Statement prepared

mysql> SET @int = 1;
Query OK, 0 rows affected (0.15 sec)

mysql> EXECUTE getJsonType USING @int, @int, @int, @int;
+---+---------------+-------------------------------------+------------------------------------------------+
| ? | JSON_ARRAY(?) | JSON_EXTRACT(JSON_ARRAY(?), "$[0]") | JSON_TYPE(JSON_EXTRACT(JSON_ARRAY(?), "$[0]")) |
+---+---------------+-------------------------------------+------------------------------------------------+
| 1 | [1]           | 1                                   | INTEGER                                        |
+---+---------------+-------------------------------------+------------------------------------------------+
1 row in set (0.13 sec)

In the code though, I get this:

conn.execute('SELECT 1, JSON_ARRAY(1), JSON_EXTRACT(JSON_ARRAY(1), "$[0]"), JSON_TYPE(JSON_EXTRACT(JSON_ARRAY(1), "$[0]"));', [], ...);

+---+---------------+-------------------------------------+------------------------------------------------+
| 1 | JSON_ARRAY(?) | JSON_EXTRACT(JSON_ARRAY(?), "$[0]") | JSON_TYPE(JSON_EXTRACT(JSON_ARRAY(?), "$[0]")) |
+---+---------------+-------------------------------------+------------------------------------------------+
| 1 | [1]           | 1                                   | INTEGER                                        |
+---+---------------+-------------------------------------+------------------------------------------------+

conn.execute('SELECT ?, JSON_ARRAY(?), JSON_EXTRACT(JSON_ARRAY(?), "$[0]"), JSON_TYPE(JSON_EXTRACT(JSON_ARRAY(?), "$[0]"));', [1, 1, 1, 1], ...);

+-----+---------+-------------------------------------+------------------------------------------------+
|  ?  | JSON_ARRAY(?) | JSON_EXTRACT(JSON_ARRAY(?), "$[0]") | JSON_TYPE(JSON_EXTRACT(JSON_ARRAY(?), "$[0]")) |
+-----+---------------+-------------------------------------+------------------------------------------------+
| "1" | ["1"]         | "1"                                 | STRING                                         |
+-----+---------------+-------------------------------------+------------------------------------------------+

[sidorares]

thanks for more details! I was doing .query() in my test, not execute(). I think the problem might be related to the fact that with prepares statements all parameters are sent as strings -

node-mysql2/lib/packets/execute.js

Line 89 in aff0b1c

packet.writeInt16(Types.VAR_STRING);

[kevinmartin]

#353 fixes this.