| Bug #79552 | json_extract seems not working propery | ||
|---|---|---|---|
| Submitted: | 8 Dec 2015 6:11 | Modified: | 22 Dec 2015 13:40 |
| Reporter: | Shinya Sugiyama | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S2 (Serious) |
| Version: | 5.7.10, 5.7.11 | OS: | CentOS (6) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | json, mysql5.7, regression | ||
[8 Dec 2015 7:06]
Shinya Sugiyama
If I trim data, it seems working fine. It was not necessary with MySQL5.7.9 but MySQL5.7.10. I don't change any sql_mode between 5.7.9 and 5.7.10. root@localhost [NEW57]> select count(*) from NEW57.features where json_extract(feature,'$.properties.STREET') = '"MARKET"'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.66 sec) root@localhost [NEW57]> select count(*) from NEW57.features where feature_street = '"MARKET"'; +----------+ | count(*) | +----------+ | 808 | +----------+ 1 row in set (0.00 sec) root@localhost [NEW57]> select count(*) from NEW57.features where rtrim(json_extract(feature,'$.properties.STREET')) = '"MARKET"'; +----------+ | count(*) | +----------+ | 808 | +----------+ 1 row in set (0.69 sec) root@localhost [NEW57]>
[8 Dec 2015 7:14]
Shinya Sugiyama
[additional confirmation]
root@localhost [NEW57]> select json_extract(`feature`,'$.properties.STREET'),CHAR_LENGTH(json_extract(`feature`,'$.properties.STREET')) from features limit 1;
+-----------------------------------------------+------------------------------------------------------------+
| json_extract(`feature`,'$.properties.STREET') | CHAR_LENGTH(json_extract(`feature`,'$.properties.STREET')) |
+-----------------------------------------------+------------------------------------------------------------+
| "UNKNOWN" | 9 |
+-----------------------------------------------+------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [NEW57]> select rtrim(json_extract(`feature`,'$.properties.STREET')),CHAR_LENGTH(rtrim(json_extract(`feature`,'$.properties.STREET'))) from features limit 1;
+------------------------------------------------------+-------------------------------------------------------------------+
| rtrim(json_extract(`feature`,'$.properties.STREET')) | CHAR_LENGTH(rtrim(json_extract(`feature`,'$.properties.STREET'))) |
+------------------------------------------------------+-------------------------------------------------------------------+
| "UNKNOWN" | 9 |
+------------------------------------------------------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [NEW57]> select * from features where rtrim(json_extract(`feature`,'$.properties.STREET')) = '"UNKNOWN"' limit 1;
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| id | feature | feature_type | feature_street |
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| 1 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}} | "Feature" | "UNKNOWN" |
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
1 row in set (0.00 sec)
root@localhost [NEW57]> select * from features where json_extract(`feature`,'$.properties.STREET') = '"UNKNOWN"' limit 1;
Empty set (0.95 sec)
root@localhost [NEW57]>
[9 Dec 2015 6:38]
MySQL Verification Team
Hello Sugiyama, Thank you for the report and test case. Observed that 5.7.11 is affected. Thanks, Umesh
[9 Dec 2015 6:39]
MySQL Verification Team
// 5.7.11 affected
mysql>
mysql> \! cat docs/INFO_SRC
commit: a4d2f3ebc729bfd339f2176ae732e15990d7ded3
date: 2015-12-02 16:06:58 +0100
build-date: 2015-12-02 18:02:15 +0100
short: a4d2f3e
branch: mysql-5.7
MySQL source 5.7.11
mysql> use test
Database changed
mysql> CREATE TABLE `features` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `feature` json NOT NULL,
-> `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.type')) VIRTUAL,
-> `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL,
-> PRIMARY KEY (`id`),
-> KEY `idx_feature_type` (`feature_type`),
-> KEY `idx_feature_street` (`feature_street`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into features(feature) values('{"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}}');
Query OK, 1 row affected (0.01 sec)
mysql> select * from features\G
*************************** 1. row ***************************
id: 206561
feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}}
feature_type: "Feature"
feature_street: "UNKNOWN"
1 row in set (0.00 sec)
mysql> select * from features where json_extract(`feature`,'$.properties.STREET') = '"UNKNOWN"';
Empty set (0.00 sec)
mysql> select * from features where rtrim(json_extract(`feature`,'$.properties.STREET')) = '"UNKNOWN"'\G
*************************** 1. row ***************************
id: 206561
feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}}
feature_type: "Feature"
feature_street: "UNKNOWN"
1 row in set (0.00 sec)
[9 Dec 2015 6:39]
MySQL Verification Team
// 5.7.9 - not affected
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE `features` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `feature` json NOT NULL,
-> `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.type')) VIRTUAL,
-> `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL,
-> PRIMARY KEY (`id`),
-> KEY `idx_feature_type` (`feature_type`),
-> KEY `idx_feature_street` (`feature_street`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into features(feature) values('{"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}}');
Query OK, 1 row affected (0.03 sec)
mysql> select * from features\G
*************************** 1. row ***************************
id: 206561
feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}}
feature_type: "Feature"
feature_street: "UNKNOWN"
1 row in set (0.00 sec)
mysql> select * from features where json_extract(`feature`,'$.properties.STREET') = '"UNKNOWN"'\G
*************************** 1. row ***************************
id: 206561
feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}}
feature_type: "Feature"
feature_street: "UNKNOWN"
1 row in set (0.00 sec)
mysql> select * from features where rtrim(json_extract(`feature`,'$.properties.STREET')) = '"UNKNOWN"'\G
*************************** 1. row ***************************
id: 206561
feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}}
feature_type: "Feature"
feature_street: "UNKNOWN"
1 row in set (0.00 sec)
[9 Dec 2015 6:45]
Shinya Sugiyama
Umesh-san, Thank you for your quick verification as always. Best Regard Shinya
[17 Dec 2015 14:04]
Knut Anders Hatlen
Hi Shinya, I think the behaviour you observed in 5.7.9 was actually a bug. The bug was fixed in 5.7.10, which is why you see different results now. There is some discussion about this in bug#78464 and bug#76834.
[18 Dec 2015 0:41]
Shinya Sugiyama
Knut-san, Thank you for your confirmation. I read bug report "bug#76834"; however, I'm not still sure about how come I need to put rtrim for selecting data from json documents. select data with generated columns that created with json_extract is working fine without rtrim... ■ With Generated Column and Index (It seems generated column and Index is working fine.) root@localhost [NEW57]> show create table features\G *************************** 1. row *************************** Table: features Create Table: CREATE TABLE `features` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feature` json NOT NULL, `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.type')) VIRTUAL, `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL, PRIMARY KEY (`id`), KEY `idx_feature_type` (`feature_type`), KEY `idx_feature_street` (`feature_street`) ) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) (1) Selecting with generated column adn Index is working fine. root@localhost [NEW57]> select id,feature_street from NEW57.features where feature_street = '"MARKET"' LIMIT 1; +-------+----------------+ | id | feature_street | +-------+----------------+ | 12250 | "MARKET" | +-------+----------------+ 1 row in set (0.00 sec) root@localhost [NEW57]> explain select id,feature_street from NEW57.features where feature_street = '"MARKET"' LIMIT 1; +----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | features | NULL | ref | idx_feature_street | idx_feature_street | 123 | const | 808 | 100.00 | Using index | +----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) (2) selecting with json_extract. If I put rtrim, it is working fine. root@localhost [NEW57]> select id,feature_street from NEW57.features where json_extract(feature,'$.properties.STREET') = '"MARKET"' limit 1; Empty set (0.85 sec) root@localhost [NEW57]> explain select id,feature_street from NEW57.features where json_extract(feature,'$.properties.STREET') = '"MARKET"' limit 1; +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | features | NULL | ALL | NULL | NULL | NULL | NULL | 185192 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) root@localhost [NEW57]> select id,feature_street from NEW57.features where rtrim(json_extract(feature,'$.properties.STREET')) = '"MARKET"' limit 1; +-------+----------------+ | id | feature_street | +-------+----------------+ | 12250 | "MARKET" | +-------+----------------+ 1 row in set (0.03 sec) root@localhost [NEW57]> explain select id,feature from NEW57.features where rtrim(json_extract(feature,'$.properties.STREET')) = '"MARKET"' limit 1; +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | features | NULL | ALL | NULL | NULL | NULL | NULL | 185192 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ■ Not Generated Columns root@localhost [NEW57]> insert into features_no_gc select id,feature from features; Query OK, 206560 rows affected (6.74 sec) Records: 206560 Duplicates: 0 Warnings: 0 root@localhost [NEW57]> show create table features_no_gc\G *************************** 1. row *************************** Table: features_no_gc Create Table: CREATE TABLE `features_no_gc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feature` json NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) root@localhost [NEW57]> select id,feature from NEW57.features_no_gc where json_extract(feature,'$.properties.STREET') = '"MARKET"' limit 1; Empty set (0.75 sec) root@localhost [NEW57]> select id,feature from NEW57.features_no_gc where rtrim(json_extract(feature,'$.properties.STREET')) = '"MARKET"' limit 1; +-------+----------------------------------------------------------------------------------------------------------------------------------------------- | id | feature |+-------+---------------------------------------------------------------------------------------------------------------------------------------------- | 12250 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.792 +-------+----------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.02 sec) root@localhost [NEW57]> root@localhost [NEW57]> explain select id,feature from NEW57.features_no_gc where rtrim(json_extract(feature,'$.properties.STREET')) = '"MARKET"' limit 1; +----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | features_no_gc | NULL | ALL | NULL | NULL | NULL | NULL | 176601 | 100.00 | Using where | +----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) root@localhost [NEW57]>
[18 Dec 2015 1:08]
Shinya Sugiyama
Knut-san, I got difference between "selecting with json_extract()" and a "selecting data with generated column that created by using json_extract()". json_extract() = require single quote only. generated column with json_extract() = require single quote and double quote for strings. ■ selecting from Json column directry using json_extract doesn't require double quote for strings. root@localhost [NEW57]> SELECT JSON_SEARCH(feature,'one', 'MARKET') AS extract_path FROM features WHERE id = 12250 ; +-----------------------+ | extract_path | +-----------------------+ | "$.properties.STREET" | +-----------------------+ 1 row in set (0.00 sec) root@localhost [NEW57]> select id,feature_street from NEW57.features where json_extract(feature,'$.properties.STREET') = 'MARKET' limit 1; +-------+----------------+ | id | feature_street | +-------+----------------+ | 12250 | "MARKET" | +-------+----------------+ 1 row in set (0.03 sec) ■ selecting data from Generated Column is need to use "" for data. root@localhost [NEW57]> select id,feature_street from NEW57.features where feature_street = 'MARKET' LIMIT 1; Empty set (0.00 sec) root@localhost [NEW57]> select id,feature_street from NEW57.features where feature_street = '"MARKET"' LIMIT 1; +-------+----------------+ | id | feature_street | +-------+----------------+ | 12250 | "MARKET" | +-------+----------------+ 1 row in set (0.00 sec) root@localhost [NEW57]> Is this official instruction?
[22 Dec 2015 11:16]
Knut Anders Hatlen
Hi Shinya, Your generated column is defined as `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL Since JSON_EXTRACT returns a value of type JSON, and the column is of type VARCHAR, the values in the generated column are implicitly converted from JSON to VARCHAR. It's as if the generated column expression had been CAST(JSON_EXTRACT(....) AS CHAR). Casting a JSON value to CHAR adds quotes around strings so that the result is a parsable JSON text. So if you compare the result to another string, that string needs double-quotes too. That's why you need to write feature_street = '"MARKET"'. You're comparing two string values. The string value in the feature_street column has double-quotes around it, so it won't match unless the string you're comparing to also has double-quotes. On the other hand, if you write JSON_EXTRACT(feature, '$.properties.STREET') = 'MARKET', you're comparing a JSON value with a MySQL string. The JSON value is not implicitly cast to CHAR in this case, so no double-quotes are added. In your case with rtrim, the JSON value is implicitly cast to CHAR, as if you had written rtrim(cast(json_extract(feature,'$.properties.STREET') as char)) = '"MARKET"' This implicit cast again adds double-quotes around the JSON value returned from JSON_EXTRACT. https://dev.mysql.com/doc/refman/5.7/en/generated-column-index-optimizations.html recommends that string columns generated from a JSON value use JSON_UNQUOTE in the column definition. If your generated column had been defined as feature_street varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) you should be able to use both of these predicates without extra double-quotes: JSON_EXTRACT(feature, '$.properties.STREET') = 'MARKET' feature_street = 'MARKET'
[22 Dec 2015 12:47]
Shinya Sugiyama
Knut-san, Thank you for your detail explanation. I understand those specifications of JSON and Generated Column now. >It's as if the generated column >expression had been CAST(JSON_EXTRACT(....) AS CHAR). Casting a JSON >value to CHAR adds quotes around strings so that the result is a >parsable JSON text. So if you compare the result to another string, that >string needs double-quotes too. >feature_street varchar(30) GENERATED ALWAYS AS >(json_unquote(json_extract(`feature`,'$.properties.STREET'))) > >you should be able to use both of these predicates without extra >double-quotes: I will share those information with other members. Best Regard Merry X'mas and Happy New Year Shinya
[22 Dec 2015 13:40]
Shinya Sugiyama
Just finished to confirm it.
Thank you.
CREATE TABLE `features_with_unquote` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`feature` json NOT NULL,
`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.type'))) VIRTUAL,
`feature_street` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `idx_feature_type` (`feature_type`),
KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4;
root@localhost [NEW57]> insert into features_with_unquote(id,feature) select id,feature from features;
Query OK, 206560 rows affected (9.62 sec)
Records: 206560 Duplicates: 0 Warnings: 0
root@localhost [NEW57]> select * from NEW57.features_with_unquote where json_extract(feature,'$.properties.STREET') = 'MARKET' limit 1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| id | feature | feature_type | feature_street |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| 12250 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}} | Feature | MARKET |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
1 row in set (0.04 sec)
root@localhost [NEW57]>
root@localhost [NEW57]> select * from NEW57.features_with_unquote where feature_street = 'MARKET' limit 1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| id | feature | feature_type | feature_street |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| 12250 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}} | Feature | MARKET |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
1 row in set (0.00 sec)
root@localhost [NEW57]>
Best Regard
Description: If I use generated columns for JSON document, I can select appropriate documents. However, if I use "json_extract()" function, it seems not working properly. Is this cast() issue? It was working properly with MySQL5.7.9. [Data in the table] root@localhost [NEW57]> select * from NEW57.features limit 1; +----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+ | id | feature | feature_type | feature_street | +----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+ | 1 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.42200352825247, 37.80848009696725, 0], [-122.42207601332528, 37.808835019815085, 0], [-122.42110217434865, 37.808803534992904, 0], [-122.42106256906727, 37.80860105681814, 0], [-122.42200352825247, 37.80848009696725, 0]]]}, "properties": {"TO_ST": "0", "BLKLOT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001", "ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM": "0001", "MAPBLKLOT": "0001001"}} | "Feature" | "UNKNOWN" | +----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+ 1 row in set (0.00 sec) root@localhost [NEW57]> How to repeat: root@localhost [NEW57]> select @@version; +-------------------------------------------+ | @@version | +-------------------------------------------+ | 5.7.10-enterprise-commercial-advanced-log | +-------------------------------------------+ 1 row in set (0.00 sec) root@localhost [NEW57]> root@localhost [NEW57]> show create table features\G *************************** 1. row *************************** Table: features Create Table: CREATE TABLE `features` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feature` json NOT NULL, `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.type')) VIRTUAL, `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL, PRIMARY KEY (`id`), KEY `idx_feature_type` (`feature_type`), KEY `idx_feature_street` (`feature_street`) ) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) root@localhost [NEW57]> root@localhost [NEW57]> select count(*) from NEW57.features where json_extract(feature,'$.properties.STREET') = '"MARKET"'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.55 sec) root@localhost [NEW57]> select count(*) from NEW57.features where feature_street = '"MARKET"'; +----------+ | count(*) | +----------+ | 808 | +----------+ 1 row in set (0.00 sec) root@localhost [NEW57]> Suggested fix: Both of them should response same output. 1) select count(*) from NEW57.features where json_extract(feature,'$.properties.STREET') = '"MARKET"'; 2) select count(*) from NEW57.features where feature_street = '"MARKET"';