so i want to pull the data from bigquery using its api, im using /queries endpoint for the same,
the event_params have different keys (page_location, ga_session_id, ga_session_number, engaged_session_event, page_title), and also different values (int_value, string_value, float_value, double_value). the problem is its coming in different objects, e.g., in one object only int_value is there and no other and, in another record, only string_value is there. now i want to have a single record for a single timestamp such that in one object only i have all 4 event_params.value. i want the result to have one object that have all the event_params.key and event_params.value sort of groupBy
query i am using:
"query":"SELECT geo.country, event_timestamp, event_params.key, event_params.value.int_value, event_params.value.string_value, event_params.value.float_value, event_params.value.double_value, event_date, event_name FROM [big-query-**************_20231017] where event_name = 'page_view' LIMIT 100"
output i am getting:
{
"kind": "bigquery#queryResponse",
"schema": {
"fields": [
{
"name": "geo_country",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "event_timestamp",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "event_params_key",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "event_params_value_int_value",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "event_params_value_string_value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "event_params_value_float_value",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "event_params_value_double_value",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "event_date",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "event_name",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
"jobReference": {
"projectId": "big-query-projectId",
"jobId": "jobId",
"location": "US"
},
"totalRows": "1250",
"pageToken": "pagetoken=",
"rows": [
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "entrances"
},
{
"v": "1"
},
{
"v": null
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "ignore_referrer"
},
{
"v": null
},
{
"v": "true"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "content"
},
{
"v": null
},
{
"v": ":::"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "ga_session_id"
},
{
"v": "169000080"
},
{
"v": null
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "anonymizeIp"
},
{
"v": null
},
{
"v": "true"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "page_title"
},
{
"v": null
},
{
"v": "some page title"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "page_type"
},
{
"v": null
},
{
"v": "Legal"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "page_url"
},
{
"v": null
},
{
"v": "https://www..com"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "page_location"
},
{
"v": null
},
{
"v": "https://www..com"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "ga_session_number"
},
{
"v": "1"
},
{
"v": null
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "engaged_session_event"
},
{
"v": "1"
},
{
"v": null
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "form_model"
},
{
"v": null
},
{
"v": "ooooo"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
},
{
"f": [
{
"v": ""
},
{
"v": "1697612281236905"
},
{
"v": "session_engaged"
},
{
"v": null
},
{
"v": "0"
},
{
"v": null
},
{
"v": null
},
{
"v": "20231017"
},
{
"v": "page_view"
}
]
}
]
}
i tried using group by event_timestamp but its giving error such as: "(L1:8): Expression 'geo.country' is not present in the GROUP BY list"