I am new to big query, i am trying to parse rows of json values to columns ,json value in a single row looks like this

  {
      "event_params": [{
        "key": "entrances",
        "value": {
          "string_value": null,
          "int_value": "1",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "ga_session_id",
        "value": {
          "string_value": null,
          "int_value": "1647758422",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_screen_class",
        "value": {
          "string_value": "Home",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_event_origin",
        "value": {
          "string_value": "auto",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "ga_session_number",
        "value": {
          "string_value": null,
          "int_value": "775",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_screen_id",
        "value": {
          "string_value": null,
          "int_value": "4463573641295231098",
          "float_value": null,
          "double_value": null
        }
      }]

}

I don't know how to parse every row in column, i want the table to look like this
key | value | string_value |int_value |float_value | double_value |
Is there a way to do it? , thank you

CodePudding user response：

Assuming your json object is in a column, you can use the json function json_extract_array combined with unnest to get all the elements of your array as a row. After that, it's up to you to choose another json function to extract the value of each element of the array.

select
    json_value(events, "$.key") as key,
    json_value(events, "$.value.string_value") as string_value,
    json_value(events, "$.value.int_value") as int_value,
     .
     .
     .
from
  example, unnest(json_extract_array(json_payload_col, "$.event_params")) as events