maria-developers team mailing list archive

[Alexey Botchkov <holyfoot@xxxxxxxxxxx>]

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 <sergey@xxxxxxxxxxx> 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
>
>
>