GA4 and BigQuery, converting event_params as columns and keeping the rest of event_params as an array of structs.

GA4_flatten_table_keep_rests_of_params.sql

SELECT event_timestamp,
       user_pseudo_id, 
       (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
       (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
       (ARRAY(SELECT AS STRUCT * FROM UNNEST(event_params) WHERE key NOT IN ('ga_session_id', 'page_title'))) AS rest_of_event_params
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
 WHERE event_name = 'page_view'
 LIMIT 1

result.json

{
  "event_timestamp": "1612069510766593",
  "user_pseudo_id": "1026454.4271112504",
  "ga_session_id": "661084800",
  "page_title": "Home",
  "rest_of_event_params": [
    {
      "key": "gclid",
      "value": {
        "string_value": null,
        "int_value": null,
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "gclsrc",
      "value": {
        "string_value": null,
        "int_value": null,
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "debug_mode",
      "value": {
        "string_value": null,
        "int_value": "1",
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "ga_session_number",
      "value": {
        "string_value": null,
        "int_value": "1",
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "all_data",
      "value": {
        "string_value": null,
        "int_value": null,
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "page_location",
      "value": {
        "string_value": "https://shop.googlemerchandisestore.com/",
        "int_value": null,
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "entrances",
      "value": {
        "string_value": null,
        "int_value": "1",
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "session_engaged",
      "value": {
        "string_value": "0",
        "int_value": null,
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "clean_event",
      "value": {
        "string_value": "gtm.js",
        "int_value": null,
        "float_value": null,
        "double_value": null
      }
    },
    {
      "key": "engaged_session_event",
      "value": {
        "string_value": null,
        "int_value": "1",
        "float_value": null,
        "double_value": null
      }
    }
  ]
}