test.sql

CREATE TEMPORARY FUNCTION
  BEGIN_DATE() AS (TIMESTAMP("2021-06-17 00:00:00 Asia/Tokyo"));
CREATE TEMPORARY FUNCTION
  END_DATE() AS (TIMESTAMP("2021-06-18 00:00:00 Asia/Tokyo"));
CREATE TEMP FUNCTION getAccessoryId(json_data STRING)
RETURNS ARRAY<INT64>
LANGUAGE js AS r"""
var values = [];
var row = JSON.parse(json_data);
for(var key in row){
    for(var i = 0; i < row[key].length; ++i) {
        values.push(row[key][i]["accessory_master_id"]);
    }
}
return values;
""";  
SELECT
  JSON_EXTRACT(val,
    '$.live_deck.accessory_info'),
  getAccessoryId(JSON_EXTRACT(val,
    '$.live_deck.accessory_info'))
FROM
  `pj-kg-kpi.llas_jp_infra.user_model_log` AS start
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP(DATE_SUB(DATE(BEGIN_DATE()), INTERVAL 1 DAY))
  AND TIMESTAMP(DATE_ADD(DATE(END_DATE()), INTERVAL 1 DAY))
  AND CAST(JSON_EXTRACT_SCALAR(val,
      '$.live_id') AS INT64) = 1623891302753824944
  AND TIMESTAMP_SECONDS(server_timestamp) >= BEGIN_DATE()
  AND TIMESTAMP_SECONDS(server_timestamp) < END_DATE()
  # AND UNNEST(JSON_EXTRACT_ARRAY(val, '$.live_deck.accessory_info')) as accessory_info
  AND category = 'user_live_start_ep1030'