Bug #96767 JSON_OBJECT with IF casts json_array to string
Submitted: 5 Sep 2019 10:02 Modified: 4 Oct 2019 12:02
Reporter: Heudon da Silva Reinaldo kbca Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: json, json_array, JSON_OBJECT

[5 Sep 2019 10:02] Heudon da Silva Reinaldo kbca
Description:
If we use json_object + if + json_array together, it quotes the json_array returned to string.

if you try any of the statements below, the result is as the expected {"test": []}

- SELECT JSON_OBJECT('test', JSON_ARRAY())
- select IF(true, JSON_ARRAY(), 1)

however, if you try the one below, the result is unexpected {"test": "[]"}
SELECT JSON_OBJECT('test', IF(true, JSON_ARRAY(), 1))

How to repeat:
statement: SELECT JSON_OBJECT('test', IF(true, JSON_ARRAY(), 1))
expected result: {"test": []}
given result: {"test": "[]"}
[5 Sep 2019 11:26] Umesh Shastry
Hello Heudon,

Thank you for the report.

regards,
Umesh
[4 Oct 2019 12:02] Knut Anders Hatlen
Hi Heudon,

I think this is intentional. When the second and third argument of IF() are not of the same type, the return type of the IF expression is the aggregated type of the arguments. The aggregated type of JSON and int is string.

To make IF() return a JSON value, both the "if" expression and the "else" expression must have type JSON. For example,

  SELECT JSON_OBJECT('test', IF(true, JSON_ARRAY(), CAST(1 AS JSON)));

returns {"test": []}.