Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 请教一个 JSON_EXTRACT 的问题
|
username: meathill
I have a question about using JSON_EXTRACT in SQL: Why can this SQL select the second piece of data?
The issue with implicit conversion is that if you don’t use coalesce, the default behavior is to compare the string converted to JSON type. In your SQL, it is converted to varchar, but both sides still have quotes, so they don’t match. Removing the quotes makes them match.
|
username: 
|
username: MySQL [test]> explain analyze select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where json_extract(info,'$.name')='Barney';
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Projection_4 | 0.80 | 1 | root | | time:688µs, loops:2, RU:0.475726, Concurrency:OFF | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info | 636 Bytes | N/A |
| └─TableReader_7 | 0.80 | 1 | root | | time:649.9µs, loops:2, cop_task: {num: 1, max: 572.1µs, proc_keys: 0, tot_proc: 2.18µs, tot_wait: 56.6µs, rpc_num: 1, rpc_time: 542.8µs, copr_cache_hit_ratio: 1.00, build_task_duration: 8.57µs, max_distsql_concurrency: 1} | data:Selection_6 | 297 Bytes | N/A |
| └─Selection_6 | 0.80 | 1 | cop[tikv] | | tikv_task:{time:12ms, loops:1}, scan_detail: {get_snapshot_time: 21.3µs, rocksdb: {block: {}}} | eq(json_extract(test.t5.info, "$.name"), cast("Barney", json BINARY)) | N/A | N/A |
| └─TableFullScan_5 | 1.00 | 1 | cop[tikv] | table:t5 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
+---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
4 rows in set (0.01 sec)
MySQL [test]> select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(json_extract(info,'$.name'),'') not in ('Barney');
+-----------------------------+------------------------------------------+-------------------------------+
| json_extract(info,'$.name') | coalesce(json_extract(info,'$.name'),'') | info |
+-----------------------------+------------------------------------------+-------------------------------+
| "Barney" | "Barney" | {"id": "3", "name": "Barney"} |
+-----------------------------+------------------------------------------+-------------------------------+
1 row in set (0.01 sec)
MySQL [test]> explain select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(json_extract(info,'$.name'),'') not in ('Barney');
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4 | 0.80 | root | | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info |
| └─TableReader_7 | 0.80 | root | | data:Selection_6 |
| └─Selection_6 | 0.80 | cop[tikv] | | ne(coalesce(cast(json_extract(test.t5.info, "$.name"), var_string(16777216)), ""), "Barney") |
| └─TableFullScan_5 | 1.00 | cop[tikv] | table:t5 | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
MySQL [test]> select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(JSON_UNQUOTE(json_extract(info,'$.name')),'') not in ('Barney');
Empty set (0.00 sec)
MySQL [test]> explain select json_extract(info,'$.name'),coalesce(json_extract(info,'$.name'),''),info from t5 where coalesce(JSON_UNQUOTE(json_extract(info,'$.name')),'') not in ('Barney');
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4 | 0.80 | root | | json_extract(test.t5.info, $.name)->Column#4, coalesce(cast(json_extract(test.t5.info, $.name), var_string(16777216)), )->Column#5, test.t5.info |
| └─TableReader_7 | 0.80 | root | | data:Selection_6 |
| └─Selection_6 | 0.80 | cop[tikv] | | ne(coalesce(json_unquote(cast(json_extract(test.t5.info, "$.name"), var_string(16777216))), ""), "Barney") |
| └─TableFullScan_5 | 1.00 | cop[tikv] | table:t5 | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)
Thank you for the guidance. I didn’t realize there was another layer. I thought extracting it would result in plain characters because it doesn’t appear to have quotes to the naked eye.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: 
|
username: 
|
username: 
|
username: