maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Sergey!

NULL is the correct standard behavior.
I don't see what syntax extension we can use to return json object.
So, I'd suggest to create a new MDEV and discuss the approaches there.

On Jun 22, Sergey Petrunia 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
> 
Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx