Hi Alexey and everyone,

I was looking at whether it's possible use JSON_TABLE to extract portions of JSON
document. Apparently it is possible in MySQL with JSON datatype:

Q1:
select *
from
  json_table('[{"color": "blue",  "price": { "high": 10, "low": 5}},
               {"color": "red",   "price": { "high": 20, "low": 8}}]',
             '$[*]' columns(color varchar(100) path '$.color',
                          price json path '$.price'
                        )
             ) as T;
+-------+------------------------+
| color | price                  |
+-------+------------------------+
| blue  | {"low": 5, "high": 10} |
| red   | {"low": 8, "high": 20} |
+-------+------------------------+


Note that if one uses any datatype other than JSON, they get NULLs:

Q2:
select *
from
  json_table('[{"color": "blue",  "price": { "high": 10, "low": 5}},
               {"color": "red",   "price": { "high": 20, "low": 8}}]',
             '$[*]' columns(color varchar(100) path '$.color',
                          price text path '$.price'
                        )
             ) as T;
+-------+-------+
| color | price |
+-------+-------+
| blue  | NULL  |
| red   | NULL  |
+-------+-------+

Oracle-the-database doesn't yet(*) have a JSON datatype. So I can only run Q2
and then I get NULLs in the price column.

MariaDB accepts JSON as datatype so query Q1 is accepted.
However the logic in MDEV-17399 code doesn't have support for dumping a portion
of JSON document, so one gets empty strings in the price column.

Should we support Q1 with JSON output in the price column?  If yes, should we
do it within the scope of MDEV-17399 or create another task for this?


(*) I see this:
https://docs.oracle.com/en/database/oracle/oracle-database/20/newft/new-json-data-type.html
BR
 Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog