MySQL Extract values from JSON type JSON Extract Operators
Example
Extract path by -> or ->> Operators, while ->> is UNQUOTED value:
SELECT
myjson_col->>'$[1]' , myjson_col->'$[1]' ,
myjson_col->>'$[*].label' ,
myjson_col->>'$[1].*' ,
myjson_col->>'$[2].*'
FROM tablename ;
-- visuall:
B, "B" , ["C"], NULL, [1, "C"]
--^^^ ^^^
So col->>path is equal to JSON_UNQUOTE(JSON_EXTRACT(col,path)) :
As with ->, the ->> operator is always expanded in the output of EXPLAIN, as the following example demonstrates:
mysql> EXPLAIN SELECT c->>'$.name' AS name -> FROM jemp WHERE g > 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: jemp partitions: NULL type: range possible_keys: i key: i key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from `jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2) 1 row in set (0.00 sec)Read about inline path extract(+)
