Hi, Sergey!
I think saving JSON values in the JSON fields (as the MySQL does) makes
sense.
So here is the patch for it
https://github.com/MariaDB/server/commit/02469bdead5753eccb5d70c98a158a07027f4eb2
Best regards.
HF
On Mon, Jun 22, 2020 at 4:48 PM Sergey Petrunia <ser...@mariadb.com> wrote:
> 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
>
>
>
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
