CODEWITHSUNDEEP
google-bigquery
Dave Chambers
Dave Chambers

Reputation: 2563

How can I insert multiple records into a specific row's repeated record field in BigQuery?

I have a very simple BigQuery table schema:

Field name          Type        Mode

thing               STRING      NULLABLE    
arraything          RECORD      REPEATED    
    quotekey        STRING      NULLABLE    
    policyvalue     INTEGER     NULLABLE    
    testbool        BOOLEAN     NULLABLE    
    testtimestamp   TIMESTAMP   NULLABLE

I inserted a row with just a thing:

INSERT INTO `...tablename`(thing) VALUES('Moose');

I need to be able to insert multiple objects into my arraything and from what I can gather the syntax for that is:

UPDATE `...tablename` SET arraything = ARRAY_CONCAT(arraything, [("a string", 6, true, NULL)]) WHERE thing = 'Moose';

However, I get the error:

Query error: No matching signature for function ARRAY_CONCAT for argument types: ARRAY<STRUCT<quotekey STRING, policyvalue INT64, testbool BOOL, ...>>, ARRAY<STRUCT<STRING, INT64, BOOL, ...>>. Supported signature: ARRAY_CONCAT(ARRAY, [ARRAY, ...]) at [36:78]

Is it not possible to use ARRAY_CONCAT with a repeated type? How would achieve inserting (multiple though one at a time for now) arraythings into a row where thing = 'Moose'? Do I need to do some casting?

Upvotes: 0

Views: 950

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Consider below fixed syntax

UPDATE `...tablename`
SET arraything = ARRAY_CONCAT(
  arraything, [
    struct<quotekey STRING, policyvalue INTEGER, testbool BOOLEAN, testtimestamp TIMESTAMP>
    ("a string", 6, true, NULL)
  ]
) 
WHERE thing = 'Moose';

Upvotes: 2

Related Questions