JSON Extract Operators

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(+)

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents