【postgres】ちょっと悩んだクエリ

やりたかったこと

• GROUP BYによる集約時
• 集約されるある列について、NULLでない行が最大1行存在することがわかっている。
• その行の値のみを取り出し、intとして取り出したい。

実行したクエリ

• ポイント1
  • JSON_AGGで配列（型はjson）を生成
• ポイント2
  • JSON_ARRAY_ELEMENTS を使い、その配列を擬似的なテーブルにみたて、NULLでない行のみを取り出す。
• ポイント3
  • 抽出された行が'null'というjsonの場合intへのキャストに失敗する。
  • そのため、'null' が入っている場合には NULL型に変換し、それ以外の場合はintでキャストする。

SELECT
    table_a.col1,
    (
        SELECT NULLIF(value#>>'{}', 'null')::int
        FROM JSON_ARRAY_ELEMENTS(JSON_AGG(table_a.col3)) 
        WHERE value IS NOT NULL 
        LIMIT 1
    ) AS agg_col3
FROM some_table
GROUP BY table_a.col1