S3 Select: Query CSV and JSON Without Full Downloads (2026)

You can query data directly within S3 buckets using S3 Select, which means you don’t have to download the entire file to your machine just to extract a few pieces of information.

Let’s see this in action. Imagine you have a CSV file in S3 named my-bucket/sales-data.csv with millions of rows, and you only want to find sales greater than $1000 from the state of California. Instead of downloading the whole file, you can run an S3 Select query like this:

aws s3api select-object-content \
    --bucket my-bucket \
    --key sales-data.csv \
    --expression "SELECT s.amount FROM S3Object s WHERE s.state = 'CA' AND s.amount > 1000" \
    --input-serialization '{"CSV": {"FieldDelimiter": ","}}' \
    --output-serialization '{"CSV": {}}' \
    --scan-range '{"Offset": 0, "Bytes": 104857600}' # Example: Scan first 100MB

This command will return only the amounts for sales that meet your criteria, directly from S3.

The core problem S3 Select solves is the inefficiency of data retrieval for analytical workloads. When dealing with large datasets in object storage like S3, downloading the entire file to perform even simple filtering or aggregation is a massive bottleneck. It consumes unnecessary network bandwidth, local storage, and processing time. S3 Select acts as an in-place query engine, allowing you to run SQL-like expressions directly on the data residing in S3.

Internally, S3 Select leverages a specialized query engine that can parse and filter data streams from S3 objects. When you issue a select-object-content API call, S3 reads the specified object (or a portion of it, as defined by scan-range), applies your expression, and streams back only the matching results. It supports both CSV and JSON formats, with specific configurations for delimiters, quoting, and JSON types (Records/Lines or JSONGenerator).

The primary levers you control are:

• --expression: This is your SQL-like query. You define the columns to select, the conditions for filtering (WHERE clause), and optionally aggregations. The syntax is similar to standard SQL but adapted for S3 object data.
• --input-serialization: This tells S3 how to interpret the data within the object. For CSV, you specify the FieldDelimiter (e.g., , or |) and potentially QuoteCharacter. For JSON, you define the Type (e.g., Records for an array of JSON objects, or Lines for JSON objects on separate lines) and JSONGenerator.
• --output-serialization: This defines the format of the results returned by S3 Select. You can choose CSV or JSON. For CSV, you can specify delimiters and quoting. For JSON, you can choose between CSV (if your query results in a single column) or JSON (if your query results in multiple columns, which will be returned as a JSON object).
• --scan-range: This is crucial for optimizing cost and performance. It allows you to specify a byte range within the object to scan. If you know your relevant data is in the first 100MB, you can set Bytes to 104857600 to avoid scanning the rest of the file. This is particularly useful for large files where only a portion contains the data you need.

The most surprising part of S3 Select is how it handles data types and schema inference. It doesn’t require a predefined schema in the traditional sense. For CSV, it infers column names based on the first row if you don’t explicitly alias them in your query (e.g., SELECT s._1, s._2 FROM S3Object s). For JSON, it navigates the nested structure using dot notation (e.g., SELECT r.user.profile.email FROM S3Object[*].records[*] r). This flexibility makes it incredibly powerful for querying semi-structured or schema-on-read data.

Beyond basic filtering, you can perform aggregations like COUNT, SUM, AVG, MIN, and MAX directly within S3 Select, further reducing the need for large data transfers.

The next hurdle you’ll likely encounter is handling very large individual objects or needing more complex join-like operations, which might push you towards services like Amazon Athena or Redshift Spectrum.