JSON の構造で SELECT したいが、値がないときは不要

Question

実現したいこと

MySQL 5.7 で、JSON で SELECT したいです。

前提

現状はJSON_ARRAYAGG(JSON_OBJECT())を使い JSON での SELECT を実現していますが、下記 発生している問題 に記載したように、値がないときにまで取得されてしまいます。（問題というか正常な動作なのでしょうけれど。）

取得後に削除すれば済む話ですが、値があるときだけ取得するよう修正できないかと模索しています。

発生している問題

下記 該当のソースコード の SELECT を実行すると、T.id がないときにまで tags の連想配列を(全てが null の値で)生成してしまいます。
こうなるのです。

[
    'id' => 1,
    'tags' => [
	    ['id' => null, 'name' => null]
    ]
]

該当のソースコード

こちらが問題の SELECT です。
JSON_ARRAYAGG(JSON_OBJECT())を使い JSON で SELECT しています。

SELECT
    C.id,

    -- tags
    JSON_ARRAYAGG(JSON_OBJECT(
        'id', T.id,
        'name', T.name
    )) AS tags

FROM
    t_comments C
    LEFT JOIN t_comment_tags CT ON CT.comment_id = C.id
    LEFT JOIN t_tags T ON T.id = CT.tag_id

WHERE
    C.supplier_id = 1
    AND C.item_kind_id = 10
    
GROUP BY C.id

試したこと

そこでT.idの有無を確認してからJSON_ARRAYAGG(JSON_OBJECT())を使うようCASE~WHENしてみました。こちらです。

SELECT
    C.id,

    -- tags
    CASE
        WHEN T.id IS NOT NULL THEN
            JSON_ARRAYAGG(JSON_OBJECT(
                'id', T.id,
                'name', T.name
            ))
        ELSE
            NULL
    END AS tags
        
FROM
    t_comments C
    LEFT JOIN t_comment_tags CT ON CT.comment_id = C.id
    LEFT JOIN t_tags T ON T.id = CT.tag_id

WHERE
    C.supplier_id = 1
    AND C.item_kind_id = 10
    
GROUP BY C.id

しかしこちらのエラーになります。

#1055 - Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_database.T.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

もちろん、「only_full_group_byの解除」という解決策は採りたくありません。

あと自力で考えられる解決策はGROUP BY C.idをGROUP BY C.id, T.id, T.name と記述することですが、汚いですよね。（実際にはC.id以外にもC.titleやC.author_idなど多数のカラムがありそれら全て記述しないといけません。）

補足情報（FW/ツールのバージョンなど）

MySQL 5.7 です。
PHP の PDO で実行します。

もしこういったケースに対する良い解決策がございましたらご指導を賜りますようお願い申し上げます。

Comments

[68user]

C.id で集約したとして、その中に複数の T.id や T.name があったらどうするの? というエラーなわけで、group by に全部列挙するか、max などの関数を使うのは自然だと思うんですが違いますかね。というか only_full_group_by なのに最初のコードはエラーにならないんでしょうか (私が何か勘違いをしている?)

[hoshi-takanori]

これ？
https://stackoverflow.com/questions/53952383/add-condition-to-mysql-json-arrayagg-function

[origa3]

68user様、どうなのでしょうか。プログラマー経験が浅く自然さの判定ができないのですが、自然でしたら安心です。

hoshi-takanori様、同じ件ですね。ありがとうございます。英語でもよくググるべきでした。申し訳ございません。

Answer 1

T.idの有無を確認してからJSON_ARRAYAGG(JSON_OBJECT())を使うようCASE~WHENして

ここですが、要は「集約値でないT.idを直接使うな」ということなので

    CASE
        WHEN sum(case when T.id is NULL then 0 else 1 end) > 0 THEN
            JSON_ARRAYAGG(JSON_OBJECT(
                'id', T.id,
                'name', T.name
            ))
        ELSE
            NULL
    END AS tags

とかだとうまくいきそうな気がします

Comments

[origa3]

お見事な案で、うまくいきました。ありがとうございます。