Как выполнять фильтрацию в оконной функции?

Question

[Богдан]

Как правильно сделать запрос в MySQL 8.0.22, чтобы отфильтровать значения в JSON_ARRAYAGG и выводить пустой массив, если соединение возвращает значение NULL? Нужно, чтобы вместо "child": [{"id": null}]} выводило "child": []

В postgres это делается через оконную функцию FILTER:

COALESCE(JSONB_AGG(
      JSON_BUILD_OBJECT(
        'id', "cartridgeId"
      )) FILTER (WHERE "cartridgeId" IS NOT NULL), '[]') AS cartridges

SELECT
  c1.category_id,
  JSON_ARRAYAGG(JSON_OBJECT('id', c2.category_id, 'child', c2.child)) AS child
FROM oc_category c1
LEFT JOIN LATERAL (
  SELECT
    c2.category_id,
    JSON_ARRAYAGG(JSON_OBJECT('id', c3.category_id)) AS child
  FROM oc_category c2
  LEFT JOIN LATERAL (
    SELECT c3.category_id FROM oc_category c3
    where c3.parent_id = c2.category_id
    ORDER BY c3.sort_order DESC
  )
  AS c3 ON true
  where c2.parent_id = c1.category_id
  GROUP BY c2.category_id
  ORDER BY c2.sort_order DESC
)
AS c2 ON true
WHERE c1.parent_id = 0
GROUP BY c1.category_id
ORDER BY c1.sort_order

Comments

[Богдан]

Bavashi, неа, тогда я потеряю всю строку

Answer 1

[Богдан]

Отвечу сам себе, нужно проверить количество записей и через условие создать пустой массив

SELECT
  c1.category_id,
  IF(count(c2.category_id), JSON_ARRAYAGG(JSON_OBJECT('id', c2.category_id, 'child', c2.child)), JSON_ARRAY()) AS child
FROM oc_category c1
LEFT JOIN LATERAL (
  SELECT
    c2.category_id,
    IF(count(c3.category_id), JSON_ARRAYAGG(JSON_OBJECT('id', c3.category_id)), JSON_ARRAY()) AS child
  FROM oc_category c2
  LEFT JOIN LATERAL (
    SELECT c3.category_id FROM oc_category c3
    where c3.parent_id = c2.category_id
    ORDER BY c3.sort_order DESC
  )
  AS c3 ON true
  where c2.parent_id = c1.category_id
  GROUP BY c2.category_id
  ORDER BY c2.sort_order DESC
)
AS c2 ON true
WHERE c1.parent_id = 0
GROUP BY c1.category_id
ORDER BY c1.sort_order