sql - 在 BigQuery 中，如何检查两个结构数组是否相等

Question

我有一个输出两个结构数组的查询:

SELECT modelId, oldClassCounts, newClassCounts
FROM `xyz`
GROUP BY 1

如果 oldClassCounts = newClassCounts，我如何创建另一个为 TRUE 的列？

这是一个 JSON 格式的示例结果:

[
  {
    "modelId": "FBF21609-65F8-4076-9B22-D6E277F1B36A",
    "oldClassCounts": [
      {
        "id": "A041EBB1-E041-4944-B231-48BC4CCE025B",
        "count": "33"
      },
      {
        "id": "B8E4812B-A323-47DD-A6ED-9DF877F501CA",
        "count": "82"
      }
    ],
    "newClassCounts": [
      {
        "id": "A041EBB1-E041-4944-B231-48BC4CCE025B",
        "count": "33"
      },
      {
        "id": "B8E4812B-A323-47DD-A6ED-9DF877F501CA",
        "count": "82"
      }
    ]
  }
]

如果 oldClassCounts 和 newClassCounts 与上面的输出完全相同，我希望相等列为 TRUE。

其他任何东西都应该是假的。

Answer 1

我会着手解决这个问题

#standardSQL
WITH xyz AS (
  SELECT "FBF21609-65F8-4076-9B22-D6E277F1B36A" AS modelId, 
      [STRUCT("A041EBB1-E041-4944-B231-48BC4CCE025B" as id, "33" as count),
      STRUCT("B8E4812B-A323-47DD-A6ED-9DF877F501CA" as id, "82" as count)] AS oldClassCounts,
      [STRUCT("A041EBB1-E041-4944-B231-48BC4CCE025B" as id, "33" as count),
      STRUCT("B8E4812B-A323-47DD-A6ED-9DF877F501CA" as id, "82" as count)] as newClassCounts),

o as (SELECT modelId, id, count, array_length(oldClassCounts) as len FROM xyz, UNNEST(oldClassCounts) as old_c),
n as (SELECT modelId, id, count, array_length(newClassCounts) as len FROM xyz, UNNEST(newClassCounts) as new_c),
uneq as (select * from o except distinct select * from n)
select xyz.*, IF(uneq.modelId is not null, false, true) as equal from xyz left join (select distinct modelId from uneq) uneq on xyz.modelId = uneq.modelId

无论数组中的顺序或重复项如何，它都有效。我们的想法是，我们将每个数组视为一个单独的临时表，删除一个中存在但另一个中不存在的所有元素(使用 except distinct)，然后对数组的长度进行额外检查，以防出现重复，例如

"FBF21609-65F8-4076-9B22-D6E277F1B36A" AS modelId, 
      [STRUCT("A041EBB1-E041-4944-B231-48BC4CCE025B" as id, "33" as count),
      STRUCT("B8E4812B-A323-47DD-A6ED-9DF877F501CA" as id, "82" as count),
      STRUCT("B8E4812B-A323-47DD-A6ED-9DF877F501CA" as id, "82" as count)]