Google BigQuery Example to converted Nested FIelds in JSON

When Kafka is integrated into a system, nested fields are often created to represent complex data structures more effectively, but this is something hard to use when we integrate with Google BigQuery.

Below is a basic query example to have some data sets from Google BigQuery

SELECT *
FROM
    `lucid-bebop-474.player_event.kafka_raw`
WHERE DATE(dt) = '2024-07-01'
ORDER BY dt DESC
LIMIT 100;

The result is displayed in the Google BigQuery Console like below:

In case that you want to convert nested_fields in Json, below example will be helpful to understand:

SELECT
	dt,
	TO_JSON_STRING(nested_fields) as json
FROM
	`lucid-bebop-474.player_event.kafka_raw`
WHERE DATE(dt) = '2024-07-02'
ORDER BY dt DESC
LIMIT 100;

The bad point for above case is that we can't make query easily, because the keyvalue is stored in the field which is named as “json” for above case.