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
• Status: Not a Bug
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0.17
• OS: Any
• 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] MySQL Verification Team

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": []}.