Export a table to a JSON file

Stay organized with collections Save and categorize content based on your preferences.

Exports a table to a newline-delimited JSON file in a Cloud Storage bucket.

Code sample


Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery C# API reference documentation.

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryExtractTableJson
    public void ExtractTableJson(
        string projectId = "your-project-id",
        string bucketName = "your-bucket-name")
        BigQueryClient client = BigQueryClient.Create(projectId);
        string destinationUri = $"gs://{bucketName}/shakespeare.json";
        var jobOptions = new CreateExtractJobOptions()
            DestinationFormat = FileFormat.NewlineDelimitedJson
        BigQueryJob job = client.CreateExtractJob(
            projectId: "bigquery-public-data",
            datasetId: "samples",
            tableId: "shakespeare",
            destinationUri: destinationUri,
            options: jobOptions
        job = job.PollUntilCompleted().ThrowOnAnyError();  // Waits for the job to complete.
        Console.Write($"Exported table to {destinationUri}.");


Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

import (


// exportTableAsJSON demonstrates using an export job to
// write the contents of a table into Cloud Storage as newline delimited JSON.
func exportTableAsJSON(projectID, gcsURI string) error {
	// projectID := "my-project-id"
	// gcsURI := "gs://mybucket/shakespeare.json"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	defer client.Close()

	srcProject := "bigquery-public-data"
	srcDataset := "samples"
	srcTable := "shakespeare"

	gcsRef := bigquery.NewGCSReference(gcsURI)
	gcsRef.DestinationFormat = bigquery.JSON

	extractor := client.DatasetInProject(srcProject, srcDataset).Table(srcTable).ExtractorTo(gcsRef)
	// You can choose to run the job in a specific location for more complex data locality scenarios.
	// Ex: In this example, source dataset and GCS bucket are in the US.
	extractor.Location = "US"

	job, err := extractor.Run(ctx)
	if err != nil {
		return err
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	if err := status.Err(); err != nil {
		return err
	return nil


Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

import com.google.cloud.RetryOption;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;
import org.threeten.bp.Duration;

public class ExtractTableToJson {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "bigquery-public-data";
    String datasetName = "samples";
    String tableName = "shakespeare";
    String bucketName = "my-bucket";
    String destinationUri = "gs://" + bucketName + "/path/to/file";
    // For more information on export formats available see:
    // https://cloud.google.com/bigquery/docs/exporting-data#export_formats_and_compression_types
    // For more information on Job see:
    // https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html

    // Note that FormatOptions.json().toString() is not "JSON" but "NEWLINE_DELIMITED_JSON"
    // Using FormatOptions Enum for this will prevent problems with unexpected format names.
    String dataFormat = FormatOptions.json().getType();

    extractTableToJson(projectId, datasetName, tableName, destinationUri, dataFormat);

  // Exports datasetName:tableName to destinationUri as a JSON file
  public static void extractTableToJson(
      String projectId,
      String datasetName,
      String tableName,
      String destinationUri,
      String dataFormat) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(projectId, datasetName, tableName);
      Table table = bigquery.getTable(tableId);

      Job job = table.extract(dataFormat, destinationUri);

      // Blocks until this job completes its execution, either failing or succeeding.
      Job completedJob =
      if (completedJob == null) {
        System.out.println("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
            "BigQuery was unable to extract due to an error: \n" + job.getStatus().getError());
          "Table export successful. Check in GCS bucket for the " + dataFormat + " file.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Table extraction job was interrupted. \n" + e.toString());


Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');
const {Storage} = require('@google-cloud/storage');

const bigquery = new BigQuery();
const storage = new Storage();

async function extractTableJSON() {
  // Exports my_dataset:my_table to gcs://my-bucket/my-file as JSON.

   * TODO(developer): Uncomment the following lines before running the sample.
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const bucketName = "my-bucket";
  // const filename = "file.json";

  // Location must match that of the source table.
  const options = {
    format: 'json',
    location: 'US',

  // Export data from the table into a Google Cloud Storage file
  const [job] = await bigquery
    .extract(storage.bucket(bucketName).file(filename), options);

  console.log(`Job ${job.id} created.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;


Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

# from google.cloud import bigquery
# client = bigquery.Client()
# bucket_name = 'my-bucket'

destination_uri = "gs://{}/{}".format(bucket_name, "shakespeare.json")
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table("shakespeare")
job_config = bigquery.job.ExtractJobConfig()
job_config.destination_format = bigquery.DestinationFormat.NEWLINE_DELIMITED_JSON

extract_job = client.extract_table(
    # Location must match that of the source table.
)  # API request
extract_job.result()  # Waits for job to complete.

What's next

To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser.