BigQuery嵌套STRUCT数组过滤后重组查询优化咨询

Problem Statement #

I'm trying to write a Google SQL (GQL) query that filters deeply nested structures, reconstructs the original nested data format, and retains the first record of STRUCT properties at the same level as ARRAYs.

Sample Schema #

WITH Sale AS (
 SELECT "1" AS _id,
 STRUCT("11" AS _id, "SERVICE" AS feedbackType, DATE(TIMESTAMP("2017-01-20 14:05:51.655")) AS createDate) AS serviceFeedback,
 [STRUCT("host" AS key, "localhost" AS value), STRUCT("location" AS key, "Paris" AS value)] AS tags,
 TRUE AS reviewed,
 [STRUCT("1" as saleId, STRUCT("101" AS _id, "PRODUCT" AS feedbackType, DATE(TIMESTAMP("2017-01-20 14:05:51.655")) AS createDate) AS productFeedback),
 STRUCT("1" as saleId, STRUCT("102" AS _id, "PRODUCT" AS feedbackType, DATE(TIMESTAMP("2017-01-20 14:06:51.655")) AS createDate) AS productFeedback) ] AS saleItems,
 DATE(TIMESTAMP("2017-01-20 14:05:51.655")) AS latestFeedbackDate
)

Current Unnested Filter Query #

This query expands all nested fields for filtering:

SELECT saleId, serviceFeedback, saleTags, reviewed, saleItems, latestFeedbackDate
FROM (
 SELECT sale._id AS saleId, serviceFeedback, sale.tags AS saleTags, reviewed, saleItems, latestFeedbackDate
 FROM `Sale` AS sale, sale.saleItems AS saleItems
 WHERE reviewed = TRUE
 AND serviceFeedback.createDate >= DATE(TIMESTAMP("2017-01-18 14:05:51.655"))
 AND serviceFeedback._id IS NOT NULL
 AND saleItems.productFeedback.createDate >= DATE(TIMESTAMP("2017-01-18 14:05:51.655")))
ORDER BY latestFeedbackDate DESC LIMIT 20

Core Requirements #

• Filter the data using the conditions above
• Group results by sale._id to reconstruct the original nested structure of saleItems
• Retain the serviceFeedback STRUCT, along with other top-level fields like tags, reviewed, and latestFeedbackDate

Expected JSON Output #

{
 "saleId":"1",
 "serviceFeedback":{"_id":"11","feedbackType":"SERVICE","createDate":"2017-01-20"},
 "saleTags":[{"key":"host","value":"localhost"},{"key":"location","value":"Paris"}],
 "reviewed":"true",
 "saleItems":[
 {"saleId":"1","productFeedback":{"_id":"101","feedbackType":"PRODUCT","createDate":"2017-01-20"}},
 {"saleId":"1","productFeedback":{"_id":"102","feedbackType":"PRODUCT","createDate":"2017-01-20"}}
 ],
 "latestFeedbackDate":"2017-01-20"
}

Current Working Solution #

I have a query that produces the correct result, but I'm looking for a more efficient implementation:

SELECT saleId, serviceFeedback, latestFeedbackDate, subQuery.saleItems as saleItems
FROM sale
RIGHT JOIN (
 SELECT saleId, ARRAY_AGG(saleItems) as saleItems
 FROM (
 SELECT saleId, saleItems
 FROM (
 SELECT sale._id AS saleId, latestFeedbackDate, saleItems
 FROM `Sale` AS sale, sale.saleItems AS saleItems
 WHERE reviewed = TRUE
 AND serviceFeedback.createDate >= DATE(TIMESTAMP("2017-01-18 14:05:51.655"))
 AND serviceFeedback._id IS NOT NULL
 AND saleItems.productFeedback.createDate >= DATE(TIMESTAMP("2017-01-18 14:05:51.655")))
 ORDER BY latestFeedbackDate DESC)
 GROUP BY saleId
 ) AS subQuery ON sale._id = subQuery.saleId

Optimized Solution #

Absolutely! You can streamline this query significantly by leveraging BigQuery's native array manipulation capabilities instead of relying on joins and nested subqueries. This approach cuts down on unnecessary data shuffling and keeps the logic easier to read.

Here's the optimized query:

WITH Sale AS (
 SELECT "1" AS _id,
 STRUCT("11" AS _id, "SERVICE" AS feedbackType, DATE(TIMESTAMP("2017-01-20 14:05:51.655")) AS createDate) AS serviceFeedback,
 [STRUCT("host" AS key, "localhost" AS value), STRUCT("location" AS key, "Paris" AS value)] AS tags,
 TRUE AS reviewed,
 [STRUCT("1" as saleId, STRUCT("101" AS _id, "PRODUCT" AS feedbackType, DATE(TIMESTAMP("2017-01-20 14:05:51.655")) AS createDate) AS productFeedback),
 STRUCT("1" as saleId, STRUCT("102" AS _id, "PRODUCT" AS feedbackType, DATE(TIMESTAMP("2017-01-20 14:06:51.655")) AS createDate) AS productFeedback) ] AS saleItems,
 DATE(TIMESTAMP("2017-01-20 14:05:51.655")) AS latestFeedbackDate
)
SELECT
  _id AS saleId,
  serviceFeedback,
  tags AS saleTags,
  reviewed,
  ARRAY(
    SELECT saleItem
    FROM UNNEST(saleItems) AS saleItem
    WHERE saleItem.productFeedback.createDate >= DATE(TIMESTAMP("2017-01-18 14:05:51.655"))
  ) AS saleItems,
  latestFeedbackDate
FROM Sale
WHERE
  reviewed = TRUE
  AND serviceFeedback.createDate >= DATE(TIMESTAMP("2017-01-18 14:05:51.655"))
  AND serviceFeedback._id IS NOT NULL
  -- Only keep sales that have at least one valid saleItem matching the filter
  AND EXISTS(
    SELECT 1
    FROM UNNEST(saleItems) AS saleItem
    WHERE saleItem.productFeedback.createDate >= DATE(TIMESTAMP("2017-01-18 14:05:51.655"))
  )
ORDER BY latestFeedbackDate DESC
LIMIT 20;

Key Improvements #

• No more joins: We avoid the RIGHT JOIN entirely by filtering and reconstructing the saleItems array directly in the main query. This eliminates the overhead of shuffling data between subqueries and joining datasets.
• Simpler logic: All filtering happens in one place—top-level conditions on the sale record, plus a targeted filter for nested saleItems. The ARRAY(SELECT ...) construct builds the filtered array without needing to unnest and re-aggregate.
• Explicit validation: The EXISTS clause ensures we only include sales that have at least one valid saleItem (matching your original filter logic where you only wanted sales with qualifying items).
• Preserves all fields: You retain every top-level property (like tags and reviewed) without having to pass them through multiple subqueries, which makes the query easier to maintain.

For larger datasets, this approach will perform noticeably better because it processes each row in a single pass, avoiding the grouping and joining steps that can slow down your original query.

内容的提问来源于stack exchange，提问作者Ray