'Get differences of two rows with nested structures in one column using BigQuery

I have a table with rows identified by "datetime" (1st column). In the 2nd column "dataset" there is an array that holds further nested structures. Ich want to do a query such that after selecting to rows (respective timepoints), I get a resulting row with the same schema that has indicators in that elements that differ (maybe a "1" or "TRUE" or whatever).

datetime dataset
2022-4-26 [ a, b, [c, [d, e] ] ]
2022-4-28 [ a, b, [c, [d, f] ] ]

Result:

datetime dataset
NULL [ a, b, [c, [d, [changed: E->f]] ] ]

Think about two JSON strings with that schema that only differ in one field.... How to get that in BigQuery?

My SQL schema:

[
  {
    "fields": [
      {
        "fields": [
          {
            "fields": [
              {
                "mode": "NULLABLE",
                "name": "permissionId",
                "type": "FLOAT"
              },
              {
                "mode": "NULLABLE",
                "name": "me",
                "type": "BOOLEAN"
              },
              {
                "mode": "NULLABLE",
                "name": "displayName",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "photoLink",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "emailAddress",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "kind",
                "type": "STRING"
              }
            ],
            "mode": "NULLABLE",
            "name": "sharingUser",
            "type": "RECORD"
          },
          {
            "fields": [
              {
                "mode": "NULLABLE",
                "name": "deleted",
                "type": "BOOLEAN"
              },
              {
                "mode": "NULLABLE",
                "name": "photoLink",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "displayName",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "role",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "kind",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "emailAddress",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "domain",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "type",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "allowFileDiscovery",
                "type": "BOOLEAN"
              },
              {
                "mode": "NULLABLE",
                "name": "pendingOwner",
                "type": "BOOLEAN"
              },
              {
                "mode": "NULLABLE",
                "name": "id",
                "type": "STRING"
              }
            ],
            "mode": "REPEATED",
            "name": "permissions",
            "type": "RECORD"
          },
          {
            "mode": "NULLABLE",
            "name": "shared",
            "type": "BOOLEAN"
          },
          {
            "fields": [
              {
                "mode": "NULLABLE",
                "name": "permissionId",
                "type": "FLOAT"
              },
              {
                "mode": "NULLABLE",
                "name": "me",
                "type": "BOOLEAN"
              },
              {
                "mode": "NULLABLE",
                "name": "displayName",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "photoLink",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "emailAddress",
                "type": "STRING"
              },
              {
                "mode": "NULLABLE",
                "name": "kind",
                "type": "STRING"
              }
            ],
            "mode": "REPEATED",
            "name": "owners",
            "type": "RECORD"
          },
          {
            "mode": "NULLABLE",
            "name": "mimeType",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "name",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "webViewLink",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "id",
            "type": "STRING"
          }
        ],
        "mode": "REPEATED",
        "name": "corporaData",
        "type": "RECORD"
      },
      {
        "mode": "NULLABLE",
        "name": "corpora",
        "type": "STRING"
      }
    ],
    "mode": "REPEATED",
    "name": "dataset",
    "type": "RECORD"
  },
  {
    "mode": "NULLABLE",
    "name": "datetime",
    "type": "STRING"
  }
]
sqlgoogle-bigquery


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions