Returning BigQuery Data in JSON Format with Python

The simplest way to interact with BigQuery is to issue SQL commands via the Python client for BigQuery.

import datetime
import decimal
import json
from google.cloud import bigquery

def get_bq_json(request):
    dataset = "hpwg-297320.movebank"
    table = "wildebeest"
    query = f"""
      SELECT
      individual_local_identifier, timestamp, location_long, location_lat,
      FROM {dataset}.{table}
      LIMIT 10000
    """

    bq_client = bigquery.Client()
    query_job = bq_client.query(query)
    results = query_job.result()
    rows = []
    for row in results:
      obj = {}
      for key, val in row.items():
        v = val
        if isinstance(val, decimal.Decimal) == True:
          v = float(val)
        if isinstance(val, datetime.datetime) == True:
          v = val.isoformat()
        obj[key] = v
      rows.append(obj)
    return json.dumps(rows)

Note that certain types, like decimal.Decimal and datetime.datetime, cannot be serialized to JSON. To avoid errors they need to be converted to serializable types like float or str, respectively.

Serving the results with Django

from django.http import HttpResponse
...
return HttpResponse(data_from_get_bq_json)

Serving the results with Flask

from flask import Response
...
return Response(data_from_get_bq_json)