MySQL Bugs: #79812: JSON_ARRAY and JSON_OBJECT return inproper JSON when used with GROUP

Bug #79812	JSON_ARRAY and JSON_OBJECT return inproper JSON when used with GROUP_CONCAT
Submitted:	30 Dec 2015 18:22	Modified:	10 Feb 2016 13:55
Reporter:	Bear Limvere	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S2 (Serious)
Version:	5.7.10, 8.0.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When you nest JSON_ARRAY and JSON_OBJECT in a SELECT query with a GROUP_CONCAT the resulting JSON is invalid.

We have written JSON utilities internally to allow returns of complex datasets from a single query. With the new 5.7 JSON functions, the resulting JSON is corrupted due to quoting.

SELECT
  department.department_name,
  JSON_ARRAY(
    GROUP_CONCAT(
      JSON_OBJECT(
        'user_id', user.user_id,
        'user_name', user.user_name,
        'is_active', user.is_active
      )
    )
  ) AS department_users
FROM temp.user
JOIN temp.department ON ( department.department_id = user.department_id)
WHERE department.department_name = 'IT'
GROUP BY department.department_name;

-- returns improper JSON
[
  "{\"user_id\": 1, \"is_active\": 1, \"user_name\": \"test user 1\"},
  {\"user_id\": 2, \"is_active\": 0, \"user_name\": \"test user 2\"}"
]

-- should return (formatted for clarity)
[
  {"user_id": 1, "is_active": 1, "user_name": "test user 1"},
  {"user_id": 2, "is_active": 0, "user_name": "test user 2"}
]

How to repeat:
CREATE DATABASE temp;
USE temp;
CREATE TABLE temp.user (
  user_id INT(11) UNSIGNED NOT NULL,
  department_id INT(11) UNSIGNED NOT NULL,
  user_name VARCHAR(32) DEFAULT NULL,
  is_active TINYINT(1) UNSIGNED NOT NULL DEFAULT 0
 );
INSERT INTO temp.user (user_id, department_id, user_name, is_active)
VALUES
  (1, 1, 'test user 1', TRUE),
  (2, 1, 'test user 2', FALSE),
  (3, 2, 'test user 3', TRUE);

CREATE TABLE temp.department (
  department_id INT(11) UNSIGNED NOT NULL,
  department_name VARCHAR(32)
);
INSERT INTO temp.department (department_id, department_name)
VALUES
  (1, 'IT'),
  (2, 'HR');

Hello Bear Limvere,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

This is actually intentional behaviour, even though it might look a bit confusing.

GROUP_CONCAT returns a string.

JSON_ARRAY does not parse string arguments as JSON text, but instead converts them to JSON string scalars.

Because of this, JSON_ARRAY(GROUP_CONCAT(...)) returns a JSON array with one element, which is a string.

There is another request for adding JSON aggregate functions, which would provide the functionality you need. See bug#79010.

For now, it should be possible to work around this lacking functionality with manual creation and parsing of JSON text. Something like this should do the trick:

CAST(CONCAT('[',
            GROUP_CONCAT(
              JSON_OBJECT(
                'user_id', user.user_id,
                'user_name', user.user_name,
                'is_active', user.is_active)),
            ']')
     AS JSON)

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0