MySQL JSON_OBJECTAGG - How to fix "PDOException: SQLSTATE[22032]: <>: 3158 JSON documents may not contain NULL member names" error?

Question

I'm implementing a classic "Three-Table Tag System" using MySQL 8 and PHP:

• the first Table ("Posts") contains posts (PostId, PostTitle, PostSummary, ...);
• the second Table ("Tags") contains tags (TagId, TagName, TagSlug);
• the third Table ("TagsMap") contains tags associated to each post (PostId, TagId).

The query I wrote to show the first 10 posts is:

SELECT p.PostTitle, p.PostSummary, ..., 
JSON_OBJECTAGG(t.TagSlug, t.TagName)
FROM (SELECT ... FROM Posts WHERE ... ORDER BY ... DESC LIMIT 10) p 
LEFT JOIN TagsMap tm ON p.PostId = tm.TagId 
LEFT JOIN Tags t ON tm.TagName = t.TagId
GROUP BY p.PostId
ORDER BY p.PostId DESC

It works.

The problem araises when a post has no associated tags. In this case, I get the following error:

"PDOException: SQLSTATE[22032]: <>: 3158 JSON documents may not contain NULL member names".

In other words, JSON_OBJECTAGG's key (t.TagSlug) cannot be null.

How can I fix it?

SQL Fiddle

Expected result using JSON_OBJECTAGG:

Title 3 | Summary 3 | NULL

Title 2 | Summary 2 | {"tag-slug-2": "tag name 2", "tag-slug-3": "tag name 3"}

Title 1 | Summary 1 | {"tag-slug-1": "tag name 1"}

Answer 1

As an alternative, you can use a subquery to retrieve the tags:

SELECT PostId, PostTitle, PostSummary,
  (SELECT JSON_OBJECTAGG(t.TagSlug, t.TagName)
   FROM TagsMap tm
   JOIN Tags t ON tm.TagId = t.TagId
   WHERE tm.PostId = p.PostId
  ) TagsObject
FROM Posts p
ORDER BY PostId DESC LIMIT 10;

Output:

+--------+-----------+-------------+----------------------------------------------------------+
| PostId | PostTitle | PostSummary | TagsObject                                               |
+--------+-----------+-------------+----------------------------------------------------------+
|      3 | Title 3   | Summary 3   | NULL                                                     |
|      2 | Title 2   | Summary 2   | {"tag-name-2": "tag name 2"}                             |
|      1 | Title 1   | Summary 1   | {"tag-name-1": "tag name 1", "tag-name-2": "tag name 2"} |
+--------+-----------+-------------+----------------------------------------------------------+

See fiddle