MySQL JSON_OBJECTAGG — Как исправить ошибку «PDOException: SQLSTATE[22032]: <>: 3158 JSON-документы могут не содержать NULL-имена элементов»?
Я реализую классическую «систему тегов с тремя таблицами», используя MySQL 8 и PHP:
- первая Таблица («Сообщения») содержит публикации (PostId, PostTitle, PostSummary, ...);
- вторая Таблица («Теги») содержит теги (TagId, TagName, TagSlug);
- третья таблица («TagsMap») содержит теги, связанные с каждым сообщением (PostId, TagId).
Запрос, который я написал, чтобы показать первые 10 сообщений:
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
Оно работает.
Проблема возникает, когда у публикации нет связанных тегов. В этом случае я получаю следующую ошибку:
"PDOException: SQLSTATE[22032]: <>: 3158 JSON documents may not contain NULL member names".
Другими словами, ключ JSON_OBJECTAGG (t.TagSlug) не может иметь значение NULL.
Как я могу это исправить?
Ожидаемый результат с использованием JSON_OBJECTAGG:
Название 3 | Резюме 3 | НУЛЕВОЙ
Название 2 | Резюме 2 | {"tag-slug-2": "имя тега 2", "tag-slug-3": "имя тега 3"}
Название 1 | Резюме 1 | {"tag-slug-1": "имя тега 1"}
В документации сказано, что он возвращает NULL, если нет совпадающих строк. Есть ли у вас фактические значения NULL в таблице Tags?
В таблице «Теги» нет значений NULL.
Для полноты картины: я не получаю никаких ошибок, если использую GROUP_CONCAT(t.TagSlug), GROUP_CONCAT(t.TagName) вместо JSON_OBJECTAGG(t.TagSlug, t.TagName).
@Barmar Запрос правильный: мне нужно показать на одной странице список всех названий тегов (например, «ночлег и завтрак») и тегов-слизней (например, «ночлег и завтрак»), связанных с это десять постов.
Ваши союзные отношения кажутся совершенно неправильными. Почему вы связываете PostID с TagId, а не с PostID?
Пожалуйста, опубликуйте образец ввода и желаемый результат. Задавайте их в вопросе, а не в комментариях. Используйте обычный текст, а не скриншоты, чтобы мы могли копировать и вставлять. См. Meta.stackexchange.com/questions/356997/… о том, как публиковать таблицы.
@Barmar добавил в мой вопрос SQL Fiddle.
Я все еще думаю, что ON p.PostId = tm.TagId должно быть ON p.PostId = tm.PostId. Нет смысла использовать tm.TagId одновременно для p.PostId и t.TagId.
Запрос в скрипке имеет ON tm.TagId = t.TagId, что не совпадает с ON tm.TagName = t.TagId в вопросе.
Добавьте к вопросу ожидаемый результат.
Ответы 3
Я предполагаю, что вам нужны результаты для всех сообщений, независимо от того, есть ли у них теги или нет. Поэтому вы не можете просто использовать WHERE t.tagSlug IS NOT NULL, потому что это приведет к исключению сообщений без тегов.
Запустите два запроса: один для сообщений, имеющих хотя бы один тег, и один для сообщений, не имеющих тегов. При желании объедините результаты.
(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;
Протестировано на MySQL 8.0.37, вот результат вашего примера 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"} |
+--------+-----------+-------------+------------------------------+
Вы также можете использовать JSON_OBJECT() для создания пустого объекта во втором подзапросе, чтобы все строки содержали там объект.
@Barmar Хорошая идея - я отредактировал пример, чтобы показать это.
@Bill Karwin добавил к моему вопросу SQL Fiddle.
@AbsoluteBeginner Я протестировал данные вашего примера и отредактировал запрос. Я включил полученный результат.
В моем SQL-скрипте допущена опечатка (извините): «имя-тега-2» должно быть «тег-слизняк-2», а «имя-тег-1» должно быть «тег-слизняк-1». Но ваш результат - это именно то, что мне нужно.
Вы можете использовать UNION ALL здесь.
И почему FROM (SELECT PostId, PostTitle, PostSummary FROM Posts) p, а не просто FROM Posts p?
@Olivier Внутри есть и другие утверждения FROM (SELECT ... FROM Posts) p. Смотрите запрос в моем вопросе.
@AbsoluteBeginner В этом все еще нет необходимости. Если вы хотите добавить условие фильтрации, используйте FROM Posts p INNER JOIN ... WHERE ... GROUP BY ....
@Оливье Оливье Ты прав. Большое спасибо! Мне нужно многое узнать о MySQL...
@AbsoluteBeginner Однако для оптимизации запроса имеет смысл использовать FROM (SELECT * FROM Posts ORDER BY PostId DESC LIMIT 10) p (в обоих запросах), иначе MySQL, вероятно, придется сканировать всю таблицу Posts.
@Оливье Разве второй LIMIT 10 не лишний?
@AbsoluteBeginner На самом деле я думаю, что лучший способ — использовать CTE (см. скрипку). Вы можете добавить в CTE пункт WHERE для фильтрации сообщений.
@Olivier Ваш запрос на игру не показывает все 3 сообщения.
@AbsoluteBeginner Это потому, что я поставил LIMIT 2 в CTE.
@Olivier Посмотрите на это: sqlfiddle.com/mysql/… — Опубликуйте n. 3 отображается дважды.
В условии соединения произошла ошибка; см. это.
@Оливье. Спасибо. Я не знал «Общих табличных выражений». Как вы думаете, в моем случае они смогут улучшить производительность запроса?
@AbsoluteBeginner Запрос с CTE оптимизирован: MySQL может выполнять CTE, используя индекс PostId, и напрямую получать необходимые сообщения (без сканирования всей таблицы).
Альтернативный подход — добавить оператор IF (или CASE):
Примечание: пожалуйста, прочитайте текст ниже; этот первый пример не работает.
IF(
t.TagSlug IS NOT NULL,
JSON_OBJECTAGG(t.TagSlug, t.TagName),
JSON_OBJECT()
) AS tags
Однако вы заметите, что MySQL все еще оценивает агрегирование, даже если он должен вернуть второе значение, что приводит к той же ошибке. Я не мог понять, почему, но, вероятно, это намеренно.
Другой подход — сделать ключ t.TagSlug условным:
JSON_OBJECTAGG(
IF(t.TagSlug IS NOT NULL, t.TagSlug, "not-found"),
t.TagName
) AS tags
Однако это приведет к следующему:
Объедините эти два подхода, чтобы получить желаемый результат:
IF(
t.TagSlug IS NOT NULL,
JSON_OBJECTAGG(COALESCE(t.TagSlug, ""), t.TagName),
JSON_OBJECT()
) AS tags
Примечание. COALESCE возвращает первое ненулевое значение, поэтому оно эквивалентно использованному ранее IF(t.TagSlug IS NOT NULL). Значение может быть пустой строкой, поскольку оно все равно отбрасывается.
Результаты следующие:
Возможно, альтернативным решением может быть JSON_OBJECTAGG(IFNULL(t.TagSlug, ''), t.TagName), которое возвращает {"": null}.
В качестве альтернативы вы можете использовать подзапрос для получения тегов:
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;
Выход:
+--------+-----------+-------------+----------------------------------------------------------+
| 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"} |
+--------+-----------+-------------+----------------------------------------------------------+
См. скрипку
Какое решение вам кажется лучше с точки зрения производительности: это или CTE?
@AbsoluteBeginner Вероятно, этот вариант немного быстрее. Это проще (нет GROUP BY для вычислений).
Разве
p.PostId = tm.TagIdне должно бытьp.Postid = tm.PostId, аtm.TagName = t.TagIdбытьtm.TagId = t.TagId? Пожалуйста, опубликуйте фактический запрос, а также образец входных данных.