In BigQuery, how do I check if two ARRAY of STRUCTs are equal

user_11666359 0
Viewed 603

I have a query that outputs two array of structs:

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

How do I create another column that is TRUE if oldClassCounts = newClassCounts?

Here is a sample result in 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"
      }
    ]
  }
]

I want the equality column to be TRUE if oldClassCounts and newClassCounts are exactly the same like the output above.

Anything else should be false.

google-bigquery sql
2 Answers

I would consider comparing the result of TO_JSON_STRING function applied on both of these arrays.

In the query it would be done in the following way:

SELECT modelId, 
       oldClassCounts, 
       newClassCounts, 
       CASE WHEN TO_JSON_STRING(oldClassCounts) = TO_JSON_STRING(newClassCounts) 
           THEN true 
           ELSE false 
       END
FROM `xyz`;

I'm not sure about GROUP BY 1 part, because non of the fields are grouped or aggregated.

It is not going to work, if the order of elements in the array is going to be different. This solution is not perfect, but worked for the data you provided.

user_98327940

I would go about with this solution

#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

It works regardless of the order or having duplicates within the arrays. The idea is that we treat each of the arrays as a separate temporary table removing all elements that exist in one but not the other (using except distinct) and then have an extra check for the length of the arrays in case there are duplicates e.g.

"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)]
user_59395910
Related