[SOLVED] How do I combine multiple json_group_array for this query correctly?

How do I combine multiple json_group_array for this query correctly?

My aggregate query with group_concat selects from one table, and if there are no results it selects from another:

SELECT
    Col1,
    Col2,
    IFNULL(
        SELECT group_concat(SomeColumn) FROM RelationOne,
        SELECT group_concat(SomeColumn) FROM RelationTwo)
FROM MainTable

This produces a comma separated list from either RelationOne or RelationTwo. I want to use json_group_array instead:

SELECT
    Col1,
    Col2,
    IFNULL(
        SELECT json_group_array(SomeColumn) FROM RelationOne,
        SELECT json_group_array(SomeColumn) FROM RelationTwo)
FROM MainTable

Which does produce a JSON array with values from RelationOne, but if RelationOne doesn't have data it produces an empty array [] instead of NULL, so if there are any values from RelationTwo they will never be shown because [] != NULL.

How do I accomplish this correctly?

Solution

Add having count() > 0 to your subquery to make it return no rows if there are no values. (Yes, having works without a group by.)

Here's an example:

with numbers(x) as (values (1), (2), (3))
select
  ifnull(
    (select json_group_array(x) from numbers b where b.x < a.x),
    'None'
  )
from numbers a;

Output:

[]
[1]
[1,2]

Now adding having count() > 0:

with numbers(x) as (values (1), (2), (3))
select
  ifnull(
    (select json_group_array(x) from numbers b where b.x < a.x having count() > 0),
    'None'
  )
from numbers a;

New Output:

None
[1]
[1,2]