More succinct way to write repetitive JSON_EXTRACT?

Question

Is there a more succinct way to write this BigQuery SQL:

select
  JSON_EXTRACT_SCALAR(DATA, '$.cloned[1].name') AS cloned_1_name,
  JSON_EXTRACT(DATA,        '$.cloned[1].value') AS cloned_1_value,
  JSON_EXTRACT_SCALAR(DATA, '$.cloned[2].name') AS cloned_2_name,
  JSON_EXTRACT(DATA,        '$.cloned[2].value') AS cloned_2_value,
  JSON_EXTRACT_SCALAR(DATA, '$.cloned[3].name') AS cloned_3_name,
  JSON_EXTRACT(DATA,        '$.cloned[3].value') AS cloned_3_value,
from `foo.bar.my_table`

This array has 100 elements, so easy to copy/paste but ugly to maintain. I asked Gemini but it provided SQL that gives a syntax error of Syntax error: Unexpected keyword UNNEST at [3:5]:

WITH extracted_cloned AS (
  SELECT
    UNNEST(JSON_EXTRACT_ARRAY(DATA, '$.cloned')) AS cloned
  FROM `foo.bar.my_table`
)
SELECT
  cloned.name AS cloned_name,
  cloned.value AS cloned_value,
FROM extracted_cloned

Answer 1

Explore below approach

SELECT * EXCEPT(data)
FROM (
  SELECT data, OFFSET + 1 AS pos,
    JSON_EXTRACT_SCALAR(cloned, '$.name') AS name,
    JSON_EXTRACT(cloned, '$.value') AS VALUE
  FROM `foo.bar.my_table`,
  UNNEST(JSON_EXTRACT_ARRAY(DATA, '$.cloned')) AS cloned WITH OFFSET
)
PIVOT (ANY_VALUE(name) AS name, ANY_VALUE(VALUE) AS VALUE FOR pos IN (1,2,3,4))