Mysql Question posted in
The official MySQL documentation can be found here.

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

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"}

Tags:

3

Answers


  1. 0

    I assume you want results for all the posts, whether they have tags or not. So you can’t just use WHERE t.tagSlug IS NOT NULL, because that would leave out the posts that have no tags.

    Run two queries, one for posts that have at least one tag, and one for posts that have no tags. Optionally union the results.

    (SELECT p.PostId, p.PostTitle, p.PostSummary,
  JSON_OBJECTAGG(t.TagSlug, t.TagName) AS TagsObject
FROM (SELECT PostId, PostTitle, PostSummary FROM Posts) p
INNER JOIN TagsMap tm ON p.PostId = tm.PostId
INNER JOIN Tags t ON tm.TagId = t.TagId
GROUP BY p.PostId)
UNION
(SELECT p.PostId, p.PostTitle, p.PostSummary,
   JSON_OBJECT()
FROM (SELECT PostId, PostTitle, PostSummary FROM Posts) p
LEFT OUTER JOIN TagsMap tm ON p.PostId = tm.PostId
WHERE tm.TagId IS NULL
GROUP BY p.PostId)
ORDER BY PostId DESC;

    Tested on MySQL 8.0.37, here is the output given your example dbfiddle:

    +--------+-----------+-------------+------------------------------+
| PostId | PostTitle | PostSummary | TagsObject                   |
+--------+-----------+-------------+------------------------------+
|      3 | Title 3   | Summary 3   | {}                           |
|      2 | Title 2   | Summary 2   | {"tag-name-2": "tag name 2"} |
|      1 | Title 1   | Summary 1   | {"tag-name-1": "tag name 1"} |
+--------+-----------+-------------+------------------------------+
    Login or Signup to reply.
  2. 0

    An alternative approach is to add an IF statement (or CASE):

    Note: please read the text below; this first example doesn’t work.

    IF(
  t.TagSlug IS NOT NULL, 
  JSON_OBJECTAGG(t.TagSlug, t.TagName), 
  JSON_OBJECT()
) AS tags

    However, you’ll notice MySQL is still evaluating the aggregation, even when it should return the second value, leading to the same error. I couldn’t quite find out why but it’s probably intentional.

    Another approach would be to make the t.TagSlug key conditional:

    JSON_OBJECTAGG(
  IF(t.TagSlug IS NOT NULL, t.TagSlug, "not-found"), 
  t.TagName
) AS tags

    However, this would result in the following:

    tags
    {"not-found": null}
    {"tag-name-2": "tag name 2"}
    {"tag-name-2": "tag name 2"}

    Combine these two approaches to get your desired output:

    IF(
  t.TagSlug IS NOT NULL, 
  JSON_OBJECTAGG(COALESCE(t.TagSlug, ""), t.TagName), 
  JSON_OBJECT()
) AS tags

    Note: COALESCE returns the first non-null value, so it’s equivalent to the IF(t.TagSlug IS NOT NULL) used previously. The value can be an empty string, as it’s discarded anyway.

    Results in the following:

    tags
    {}
    {"tag-name-2": "tag name 2"}
    {"tag-name-2": "tag name 2"}
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search