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`