BigQuery is creating a STRUCT of NULLs when a row has no matching LEFT JOIN

user_11666359 0
Viewed 143

I have table called models. I have another table called labelled_data that has modelId and classId. For every row in models, I need to count how many times each classId occurs in labelled_data.

The following BigQuery result has column class that is an ARRAY of STRUCTs.

WITH labelled_data AS(
  SELECT
    modelId,
    classId,
    COUNT(*) as count 
  FROM `abc.xyz.labelled_data`
  GROUP BY 1, 2
)

SELECT
  m.id,
  ARRAY_AGG(
    STRUCT(ld.classId AS id, ld.count)
  ) AS class,
FROM `abc.xyz.models` m
LEFT JOIN labelled_data AS ld ON ld.modelId = m.id
GROUP BY 1

The problem is that if there is no matching LEFT JOIN between models and labelled_data, class contains a single STRUCT element whose elements are NULL.

Here what most rows look like in JSON format:

{
    "id": "43A4BBA0-7D3D-466B-B893-CE1397223EB6",
    "class": [
      {
        "id": "F0CD7177-2608-4DA3-90F4-69DF435E3C3B",
        "count": "42"
      },
      {
        "id": "25FB2670-FBA8-4961-B4BC-535776FDE6D3",
        "count": "40"
      }
    ]
}

Here is a problematic row:

{
    "id": "428E6D3C-98A3-4B9E-BB50-DCD589C6E321",
    "class": [
      {
        "id": null,
        "count": null
      }
    ]
}

I would like the desired result to be:

{
    "id": "428E6D3C-98A3-4B9E-BB50-DCD589C6E321",
    "class": null
}

Note that I cannot exclude that row from the results. Any ideas on how to achieve this?

google-bigquery left-join count arrays sql
1 Answers

You could use a case expression that ensures that there is at least one matching row:

WITH (...)
SELECT
    m.id,
    CASE WHEN COUNT(ld.modelid) > 0
        THEN ARRAY_AGG(STRUCT(ld.classId AS id, ld.count)) 
    END AS class,
FROM `abc.xyz.models` m
LEFT JOIN labelled_data AS ld ON ld.modelId = m.id
GROUP BY 1
user_106767160
Related