How to insert data into array BigQuery

Question

How to to insert data into the the arrays of a table (Table1) from a subquery (Table2) in BigQuery?

I tried using INSERT INTO but with no success, then I tried using UPDATE and the ARRAY_CONCAT() function, it worked, but I'm wondering if there isn't a better way to get the same result.

You can see the example below:

Table1 Using the following schema

CREATE OR REPLACE TABLE
  `project.dataset.Table1` ( column1 string,
    column2 TIMESTAMP,
    column3 ARRAY<int64>,
    column4 ARRAY<int64>)

With this data

INSERT INTO
  `project.dataset.Table1` (column1,
    column2,
    column3,
    column4)
VALUES
  ("rowtest1", CURRENT_TIMESTAMP(), [5], [5000]),
  ("rowtest2", CURRENT_TIMESTAMP(), [10], [10000]),
  ("rowtest2", CURRENT_TIMESTAMP(), [12], [12000])

Table2 Using the following schema

CREATE OR REPLACE TABLE
  `project.dataset.Table2` ( column1 string,
    column2 TIMESTAMP,
    column3 int64,
    column4 int64)

With this data

INSERT INTO
  `project.dataset.Table2` (column1,
    column2,
    column3,
    column4)
VALUES
  ("rowtest1", CURRENT_TIMESTAMP(), 7, 7000)

To insert the data from Table2 into the arrays column3 and column4 of Table1, only for the respective rows in both tables, that is: the row WHERE column1 = "rowtest1", I used the following DML and then repeated the same logic for column4.

UPDATE
  `project.dataset.Table1` t1
SET
  column3 = ARRAY_CONCAT(t1.column3, (
    SELECT
      [t2.column3]
    FROM
      `project.dataset.Table2` t2 WHERE t2.column1 = "rowtest1"))
WHERE
  t1.column1 = "rowtest1"

Is there another way to get the same result?

Pentium10 · Accepted Answer · 2022-08-12 18:19:49Z

Almost there, you need to write as

UPDATE
  `dataset.Table1` t1
SET
  column3 = ARRAY_CONCAT(t1.column3, ARRAY (
    SELECT 
      t2.column3
    FROM
      `dataset.Table2` t2 WHERE t2.column1 = "rowtest1"))
WHERE
  t1.column1 = "rowtest1"

A common task when working with arrays is turning a subquery result into an array. In BigQuery, you can accomplish this using the ARRAY() function.

For example, consider the following operation on the sequences table

WITH sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
  UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
  UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x) AS doubled
FROM sequences;

+--------------------+---------------------+
| some_numbers       | doubled             |
+--------------------+---------------------+
| [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
| [2, 4, 8, 16, 32]  | [4, 8, 16, 32, 64]  |
| [5, 10]            | [10, 20]            |
+--------------------+---------------------+

Collectives™ on Stack Overflow

How to insert data into array BigQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related