How to Use GCP Big Query Record Repeat Type

Table of Contents

1. Insert a data into Items Field
2. Query all keys from the dataset
3. Select Insert into the Record Type
4. Find all the unique Key from the table
5. Convert the record to JSON by using to_json_string()

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`