How to Update a Struck with New Rows in GBQ with Standard SQL

May 22, 2023

I have the following table:

WITH
  source_data AS (
  SELECT
    1 AS client_id,
    CAST('2022-10-13' AS DATE) AS session_date,
    'denied' AS value,
  UNION ALL
  SELECT
    1,
    CAST('2022-10-15' AS DATE),
    'granted'
  UNION ALL
  SELECT
    1,
    CAST('2022-10-18' AS DATE),
    'denied'
  UNION ALL
  SELECT
    2,
    CAST('2022-01-01' AS DATE),
    'denied'
  UNION ALL
  SELECT
    2,
    CAST('2022-01-05' AS DATE),
    'granted'
  UNION ALL
  SELECT
    3,
    CAST('2022-01-01' AS DATE),
    'granted'
  UNION ALL
  SELECT
    4,
    CAST('2022-01-03' AS DATE),
    'granted'),
  max_date AS (
  SELECT
    client_id,
    session_date,
    value
  FROM
    source_data )
SELECT
  client_id,
  MAX(session_date) AS last_activity,
  ARRAY_AGG(STRUCT(session_date,
      value)
  ORDER BY
    session_date) AS push_permission
FROM
  max_date
GROUP BY
  1

It looks like this:

Now, I have another table that create new records for client 1 and 2, like you see in the picture:

WITH
  source_data AS (
  SELECT
    1 AS client_id,
    CAST('2023-05-08' AS DATE) AS session_date,
    'denied' AS value,
  UNION ALL
  SELECT
    2,
    CAST('2023-05-08' AS DATE),
    'granted'
  ),
  max_date AS (
  SELECT
    client_id,
    session_date,
    value
  FROM
    source_data )
SELECT
  client_id,
  MAX(session_date) AS last_activity,
  ARRAY_AGG(STRUCT(session_date,
      value)
  ORDER BY
    session_date) AS push_permission
FROM
  max_date
GROUP BY
  1

I would like to know if there is any way to append these results inside the struct in the following way in the first table, like this:

I have tried with INSERT, but it is creating new records in the table, not appending them to the struct.

I have take a look to UPDATE, but I don’t see in the documentation how to use it in STRUCT, only in ARRAY. This is what I tried:

UPDATE
  `table1`
SET
  push_permission = ARRAY(
  SELECT
    push_permission
  FROM
    UNNEST(push_permission) AS push_permission
  UNION ALL
  SELECT
    (CAST(CURRENT_DATE()-1 AS DATE),
      push_permission.push_system_permission ))
  WHERE client_id IN (SELECT
    DISTINCT(client_id)
  FROM
    `table2`)

Is there any way to do this in GBQ?

>Solution :

You might consider below approach.
(you can replace the existing table with the result of below query)

WITH first_source_data AS (
-- put your first query here
),
second_source_data AS (
-- put your second query here
)
SELECT client_id,
       MAX(last_activity) last_activity,
       ARRAY_CONCAT_AGG(push_permission) push_permission
  FROM (
    SELECT * FROM first_source_data
     UNION ALL
    SELECT * FROM second_source_data
  ) GROUP BY 1;

Query result