JSON functions seem be fine with processing UTF8MB4 characters. The only exception is JSON_UNQUOTE which tries to produce UTF8MB3 and so destroys any non-mb3 characters.
Testcase: (the smiley is this character: https://www.compart.com/en/unicode/U+1F60A)
set names utf8mb4;
|
set @v='{ "color":"😊" }';
|
select @v, collation(@v);
|
select json_valid(@v);
|
select json_extract(@v,'$.color'), collation(json_extract(@v,'$.color'));
|
select json_unquote(json_extract(@v,'$.color')), collation(json_unquote(json_extract(@v,'$.color')));
|
Run it and:
MariaDB [test]> set names utf8mb4;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> set @v='{ "color":"😊" }';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> select @v, collation(@v);
|
+--------------------+-----------------------+
|
| @v | collation(@v) |
|
+--------------------+-----------------------+
|
| { "color":"😊" } | utf8mb4_uca1400_ai_ci |
|
+--------------------+-----------------------+
|
1 row in set (0.001 sec)
|
Note that JSON_VALID and JSON_EXTRACT work:
MariaDB [test]> select json_valid(@v);
|
+----------------+
|
| json_valid(@v) |
|
+----------------+
|
| 1 |
|
+----------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> select json_extract(@v,'$.color'), collation(json_extract(@v,'$.color'));
|
+----------------------------+---------------------------------------+
|
| json_extract(@v,'$.color') | collation(json_extract(@v,'$.color')) |
|
+----------------------------+---------------------------------------+
|
| "😊" | utf8mb4_uca1400_ai_ci |
|
+----------------------------+---------------------------------------+
|
1 row in set (0.001 sec)
|
But try removing the quotes and the smiley is lost:
MariaDB [test]> select json_unquote(json_extract(@v,'$.color')), collation(json_unquote(json_extract(@v,'$.color')));
|
+------------------------------------------+-----------------------------------------------------+
|
| json_unquote(json_extract(@v,'$.color')) | collation(json_unquote(json_extract(@v,'$.color'))) |
|
+------------------------------------------+-----------------------------------------------------+
|
| ? | utf8mb3_general_ci |
|
+------------------------------------------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
Bug
Critical
MDEV-35616 Add basic optimizer support for virtual columns
This function is needed if one has a JSON field, a virtual column that extracts some value from the JSON, and an index on that value. It's the primary usecase of
MDEV-35616.