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 = token_parameters.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

When specific columns are selected, only those columns are synchronized to the client.

This is good practice, to ensure the synced data does not unintentionally change when new columns are added to the schema.

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

Renaming columns

Different names (aliases) may be specified for columns:

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

Transforming columns

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

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