Skip to main content
Check the columns of published data, then filter and aggregate it on the server.

Pick the data

Search for data items with --type data. The result also lists the columns (name and type) and the row count.
Note the id in the result. You will use it in the next commands.

Check the columns

Check the column names and types before you aggregate. A name must be written exactly.
Replace <id> with the id of the data item. --detail light returns only the column names, types and the row count. To look at the rows, get the first few.
When the publisher did not include all data, the rows are the sample saved at publish time. Do totals and filters with dataset query below, not on a sample.

Filter and aggregate

This example sums the sales column for each value of the region column and returns the top 5, largest first.
Replace <dataset_id> with the data id and region with a real column name. The result below sums sales by region on the data with the id hI6jK7lM8n (Sales Orders). Result
  • --mode aggregate groups by --dimensions and calculates --metrics.
  • --mode rows gets the rows that match the conditions. Name the columns to return with --columns.
  • rows returns at most 1,000 rows and aggregate at most 500. There is no paging. When has_more in the result is true, narrow the query or aggregate.
  • The result includes the data version and update time. When you report the numbers, say which data they are based on.
To get the rows that match a condition, use --mode rows. It needs --columns, and --filters narrows the rows. This example returns two columns of the rows where region is Kanto.
operator is the comparison. The operators a data item supports are in the result of expl server dataframe get-schema <id> --detail full. For how to combine several conditions, see Filter conditions. When the result is too large, save it as a CSV or Parquet file with dataframe export. To join several data items, see the dataset query page.

Write the JSON arguments

JSON is a text format for writing structured data. Pass dimensions, metrics, sort and similar flags as JSON. Wrap the whole JSON in single quotes (’) and write the strings inside in double quotes (”). The alias in metrics is required. Use only letters, digits and _, do not start with a digit, and use at most 64 characters. In sort, name the output column with field. These are the messages you see for a wrong shape, and how to fix them.

Data that cannot be queried

Data published without its full data cannot be queried on the server. dataset query then fails with this error. Search results and --detail light do not show whether a data item can be queried. Run --detail full and check that query.available in the result is not false.