如何在BigQuery中展开多个数组？含动态字段场景示例

Got it! Let's refine that query from @mikhail-berlyant to fit your exact BigQuery data setup. Here's the adjusted solution that properly matches questions to answers using their IDs, even with your STRING-type JSON fields:

WITH parsed_data AS (
  SELECT
    token,
    -- Parse the questions string into a JSON array of field objects
    JSON_EXTRACT_ARRAY(questions, '$.fields') AS questions_array,
    -- Parse the answers string into a JSON array of answer objects
    JSON_EXTRACT_ARRAY(answers) AS answers_array
  FROM `your_project.your_dataset.your_table` -- Replace with your actual table path
),
unnested_questions AS (
  SELECT
    token,
    JSON_EXTRACT_SCALAR(question_obj, '$.id') AS question_id,
    JSON_EXTRACT_SCALAR(question_obj, '$.text') AS question_text -- Adjust key if your question text uses a different name
  FROM parsed_data,
       UNNEST(questions_array) AS question_obj
),
unnested_answers AS (
  SELECT
    token,
    JSON_EXTRACT_SCALAR(answer_obj, '$.id') AS answer_id,
    JSON_EXTRACT_SCALAR(answer_obj, '$.value') AS answer_text -- Adjust key if your answer uses a different name (e.g., "text")
  FROM parsed_data,
       UNNEST(answers_array) AS answer_obj
)
SELECT
  uq.token,
  uq.question_id,
  uq.question_text,
  ua.answer_text
FROM unnested_questions uq
INNER JOIN unnested_answers ua
  ON uq.token = ua.token
  AND uq.question_id = ua.answer_id
ORDER BY uq.token, uq.question_id;

• Handling STRING-Type JSON: Since your questions and answers are stored as STRINGs (not native JSON types), we first use JSON_EXTRACT_ARRAY to convert them into queryable JSON arrays. For questions, we directly target the nested fields array that holds your 3 questions.
• Unnesting for Matching: We break down both arrays into individual rows with UNNEST—this lets us work with each question and answer as a separate record, making ID-based matching straightforward.
• Reliable Matching: By joining on both token (to keep entries grouped together) and id (to pair each question with its correct answer), we avoid relying on the order of items in the arrays. This is crucial if your questions/answers aren't always in the same positional order.

Quick Note: If your JSON structure uses different keys (e.g., question instead of text for question content, or answer instead of value for answers), just update the JSON_EXTRACT_SCALAR paths to match your actual data.

内容的提问来源于stack exchange，提问作者sam