JSON_OBJECT inside JSON_ARRAYAGG sometimes return json array, sometimes string

Question

The JSON_OBJECT function strangely returns different data types depending on the presence of an key index in the join-table in MariaDB 10.5.12.

I've been trying to get post comments in a json array. And noticed that the output of the aggregate function changes strangely.

While it always gives the same right results, the packaging varies. Sometimes it's a escaped string requiring additional json parsing, sometimes it's a ready-made json array.

Gradually I was able to get close to the cause. Here's a minimally working piece of a query that shows the bug.

SELECT 
    JSON_ARRAYAGG(DISTINCT JSON_OBJECT(
        'comment_id', comment.id, 
              'text', comment.text
    ) ORDER BY comment.id) AS comments
FROM post
LEFT JOIN comment ON comment.post_id = post.id
LEFT JOIN vote ON vote.user_id = 1 AND vote.post_id = post.id
GROUP BY post.id

The prerequisite is that the key index user_id exists in the vote table.

If the join occurs by the user_id field condition, the output is escaped string:

LEFT JOIN vote ON vote.user_id = 123 AND vote.post_id = post.id 

["{\"tag_id\": 1, \"name\": \"conubia\"}","{\"tag_id\": 21, \"name\": \"convallis\"}"]
["{\"tag_id\": 40, \"name\": \"amet\"}","{\"tag_id\": 41, \"name\": \"neque\"}"]

If the merge does not occur by the user_id field condition, the output is json array:

LEFT JOIN vote ON vote.user_id = -1 AND vote.post_id = post.id 

[{"tag_id": 1, "name": "conubia"},{"tag_id": 21, "name": "convallis"}]
[{"tag_id": 40, "name": "amet"},{"tag_id": 41, "name": "neque"}]

If there is no key index user_id, under all join conditions, the output is escaped string:

["{\"tag_id\": 1, \"name\": \"conubia\"}","{\"tag_id\": 21, \"name\": \"convallis\"}"]
["{\"tag_id\": 40, \"name\": \"amet\"}","{\"tag_id\": 41, \"name\": \"neque\"}"]

Vote DDL

CREATE TABLE `vote` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NULL DEFAULT NULL,
    `post_id` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `user_id` (`user_id`) USING BTREE
)
ENGINE=InnoDB
AUTO_INCREMENT=1

Question

Is it possible to prevent accidental changes to the function output by means of the database, without removing the index?

Assumption

I think I could use CAST, but it didn't work for me.

Answer 1

Looks like this was connected with the bugs in JSON escaping.

• https://jira.mariadb.org/browse/MDEV-26506
• https://jira.mariadb.org/browse/MDEV-21902
• https://jira.mariadb.org/browse/MDEV-22837

The problem has been fixed in the new releases. Tested in MariaDB 10.6.5.