How to Use GCP Big Query Record Repeat Type
Create a Table so we can insert the Record Repeat type, or query the data. The blow sample test table, we created a field name as items, which is record type with key is key and value both are string.
CREATE TABLE `Test.MyTestTable` ( id STRING, items ARRAY<STRUCT<key STRING, value STRING>> );
Insert a data into Items Field
This SQL script will insert key/value pair data into the items Record repeated field.
insert into `Test.MyTestTable` (id, items) select safe_cast(current_timestamp() as string), array[ struct('country' as key, "UK" as value), struct('browser' as key, "chrome" as value), struct('device' as key, "desktop" as value) ]
Query all keys from the dataset
This will return all keys from the table
SELECT x.key FROM `Test.MyTestTable`, unnest(items) as x;
Select Insert into the Record Type
Below example is join the *my_temp_table table and as select insert into the record field.
create TEMP TABLE my_temp_table ( country string, device string, browser string ); INSERT INTO my_temp_table (country, device, browser) SELECT "US" AS country, "mobile" AS device, "safari" AS browser; INSERT INTO `Test.MyTestTable` (id, items) SELECT SAFE_CAST(CURRENT_TIMESTAMP() AS string), ARRAY[ STRUCT('country' AS KEY, my_temp_table.country AS value), STRUCT('browser' AS KEY, my_temp_table.browser AS value), STRUCT('device' AS KEY, my_temp_table.device AS value) ] FROM my_temp_table
Find all the unique Key from the table
This query is to find unique key from the record type.
SELECT distinct(x.key), count(*) FROM `Test.MyTestTable`, unnest(items) as x group by 1 order by 2 desc ;
Convert the record to JSON by using to_json_string()
select id, to_json_string(items) from `Test.MyTestTable`