data-queries.mdx

title	Data Queries

Data queries select the data that form part of a bucket, using the bucket parameters.

Multiple data queries can be specified for a single bucket definition.

**Data queries are used to group data into buckets, so each data query must use every bucket parameter.**

Examples

Grouping by list_id

bucket_definitions:
  owned_lists:
    parameters: |
        SELECT id as list_id FROM lists WHERE
           owner_id = request.user_id()
    data:
      - SELECT * FROM lists WHERE lists.id = bucket.list_id
      - SELECT * FROM todos WHERE todos.list_id = bucket.list_id

Selecting output columns/fields

When specific columns/fields are selected, only those columns/fields are synced to the client.

This is good practice, to ensure the synced data does not unintentionally change when new columns are added to the schema (in the case of Postgres) or to the data structure (in the case of MongoDB).

Note: An id column must always be present, and must have a text type. If the primary key is different, use a column alias and/or transformations to output a text id column.

bucket_definitions:
  global:
    data:
      - SELECT id, name, owner_id FROM lists

MongoDB uses `_id` as the name of the ID field in collections. Therefore, PowerSync requires using `SELECT _id as id` in the data queries when [using MongoDB](/installation/database-setup) as the backend source database.

Renaming columns/fields

Different names (aliases) may be specified for columns/fields:

bucket_definitions:
  global:
    data:
      - SELECT id, name, created_timestamp AS created_at FROM lists

Transforming columns/fields

A limited set of operators and functions are available to transform the output value of columns/fields.

bucket_definitions:
  global:
    data:
      # Cast number to text
      - SELECT id, item_number :: text AS item_number FROM todos
      # Alternative syntax for the same cast
      - SELECT id, CAST(item_number as TEXT) AS item_number FROM todos
      # Convert binary data (bytea) to base64
      - SELECT id, base64(thumbnail) AS thumbnail_base64 FROM todos
      # Extract field from JSON or JSONB column
      - SELECT id, metadata_json ->> 'description' AS description FROM todos
      # Convert time to epoch number
      - SELECT id, unixepoch(created_at) AS created_at FROM todos