Table: trello_card - Query Trello Cards using SQL

A Trello Card is a fundamental unit in Trello, a popular project management tool. Cards represent tasks and contain information such as descriptions, comments, attached files, and checklists. They can be assigned to specific team members, labeled for quick identification, and moved across different lists representing various stages of a project.

Table Usage Guide

The trello_card table provides insights into the tasks within Trello. As a project manager or team member, explore card-specific details through this table, including descriptions, comments, attached files, and checklists. Utilize it to uncover information about tasks, such as their current stage, assigned members, and associated labels.

Important Notes

You must specify the id_list in the where or join clause (where id_list=, join trello_list l on l.id=) to query this table.

Examples

Basic info

Determine the areas in which specific tasks are assigned by identifying the details of Trello cards within a particular list. This helps in understanding the task distribution and planning future tasks effectively.

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b';

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b';

Get all cards in a list that are past due

Explore which tasks in a specific project are overdue and still open. This is useful for tracking project progress and identifying areas that may require additional resources or attention.

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and due < now()
  and not closed;

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and due < datetime('now')
  and not closed;

Get all cards in a list that are due in a week

Determine the tasks in a specific project that are due in the upcoming week, to help prioritize work and manage deadlines effectively. This query is particularly useful for project managers keeping track of task deadlines and ensuring project progress.

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and due < now() + interval '7 days'
  and not closed;

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and due < datetime('now', '+7 days')
  and not closed;

Get all cards in a list in order of their due date, with the most recent due first

Identify tasks in a specific project that are still ongoing, arranged based on their deadlines starting from the most urgent. This helps in prioritizing work and ensuring timely completion of tasks.

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and not closed
order by
  due desc;

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and not closed
order by
  due desc;

List all cards attachments

This query allows you to identify all attachments linked to a specific task in a project management tool. It provides valuable insights into the resources associated with a task, which can aid in task comprehension and completion.

select
  id,
  name,
  a ->> 'id' as attachment_id,
  a ->> 'name' as attachment_name,
  a ->> 'url' as attachment_url,
  a ->> 'date' as attachment_date,
  a ->> 'edgeColor' as attachment_edge_color,
  a ->> 'idMember' as attachment_id_member,
  a ->> 'mimeType' as attachment_mime_type,
  a ->> 'pos' as attachment_pos
from
  trello_card c,
  jsonb_array_elements(c.attachments) as a
where
  id_list = '123ace54605094aa59b02c4b';

select
  c.id,
  c.name,
  json_extract(a.value, '$.id') as attachment_id,
  json_extract(a.value, '$.name') as attachment_name,
  json_extract(a.value, '$.url') as attachment_url,
  json_extract(a.value, '$.date') as attachment_date,
  json_extract(a.value, '$.edgeColor') as attachment_edge_color,
  json_extract(a.value, '$.idMember') as attachment_id_member,
  json_extract(a.value, '$.mimeType') as attachment_mime_type,
  json_extract(a.value, '$.pos') as attachment_pos
from
  trello_card c,
  json_each(c.attachments) as a
where
  c.id_list = '123ace54605094aa59b02c4b';

List all badges for a card

Discover the segments that have all the badges assigned to a specific card. This is beneficial in understanding the attributes or achievements associated with that card, providing insights into its importance or role within the project.

select
  id,
  name,
  b.key as badge_key,
  b.value as badge_value
from
  trello_card c,
  jsonb_each_text(badges) b
where
  id_list = '123ace54605094aa59b02c4b';

select
  c.id,
  c.name,
  b.key as badge_key,
  b.value as badge_value
from
  trello_card c,
  json_each(c.badges) as b
where
  c.id_list = '123ace54605094aa59b02c4b';

List all cards with a specific label

Discover the segments that have been marked as 'Blocked' within a specific Trello list. This can help in identifying bottlenecks or issues that are hindering progress in a project.

select
  id,
  name,
  l ->> 'id' as label_id,
  l ->> 'name' as label_name,
  l ->> 'color' as label_color
from
  trello_card c,
  jsonb_array_elements(labels) l
where
  id_list = '123ace54605094aa59b02c4b'
  and l ->> 'name' = 'Blocked';

select
  c.id,
  c.name,
  json_extract(l.value, '$.id') as label_id,
  json_extract(l.value, '$.name') as label_name,
  json_extract(l.value, '$.color') as label_color
from
  trello_card c,
  json_each(c.labels) as l
where
  c.id_list = '123ace54605094aa59b02c4b'
  and json_extract(l.value, '$.name') = 'Blocked';

List all the member details assigned to a card

Explore which members are associated with a specific card in your Trello board. This can help in understanding task delegation and tracking the progress of individual team members.

select
  c.id,
  c.name,
  m.id as member_id,
  m.username as member_username,
  m.full_name as member_full_name,
  m.initials as member_initials
from
  trello_card c,
  trello_member m,
  jsonb_array_elements_text(c.id_members) mid
where
  id_list = '123ace54605094aa59b02c4b'
  and m.id = mid;

select
  c.id,
  c.name,
  m.id as member_id,
  m.username as member_username,
  m.full_name as member_full_name,
  m.initials as member_initials
from
  trello_card c,
  trello_member m,
  json_each(c.id_members) mid
where
  id_list = '123ace54605094aa59b02c4b'
  and m.id = mid.value;

List all open cards in a list

Explore which tasks are currently active on a specific Trello list. This is useful for monitoring project progress and identifying outstanding tasks.

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and not closed;

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and not closed;

List all cards in a list that have been subscribed to

Explore which tasks within a specific task list you are actively following. This is useful for keeping track of important tasks and ensuring you don't miss any updates.

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and subscribed;

select
  id,
  name,
  description,
  id_board,
  id_list,
  start,
  due,
  closed
from
  trello_card
where
  id_list = '123ace54605094aa59b02c4b'
  and subscribed;

Schema for trello_card

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
attachments	jsonb		The attachments of the card.
badges	jsonb		The badges of the card.
check_item_states	jsonb		The check item states of the card.
checklists	jsonb		The checklists of the card.
closed	boolean		Indicates whether the card is closed.
custom_field_items	jsonb		The custom field items of the card.
custom_field_map	jsonb		The custom field map of the card.
date_last_activity	timestamp with time zone		The timestamp of the last activity on the card.
description	text		The description or summary of the card.
due	timestamp with time zone		The due date of the card, if set.
due_complete	boolean		Indicates whether the due date of the card is complete.
email	text		The email id associated with the card.
id	text	=	The unique identifier for the card.
id_attachment_cover	text		The id of the attachment used as the card cover.
id_board	text		The id of the board the card belongs to.
id_check_lists	jsonb		The ids of checklists attached to the card.
id_labels	jsonb		The ids of labels attached to the card.
id_list	text	=	The id of the list the card belongs to.
id_members	jsonb		The ids of members attached to the card.
id_members_voted	jsonb		The ids of members who voted on the card.
id_short	text		The short id of the card.
labels	jsonb		The labels of the card.
manual_cover_attachment	boolean		The manual cover attachment of the card.
name	text		The name of the card.
pos	bigint		The position of the card.
short_link	text		The shortened link of the card.
short_url	text		The shortened URL of the card.
start	timestamp with time zone		The start time of the card.
subscribed	boolean		Indicates whether the card has been subscribed.
title	text		The title of the card.
url	text		The URL of the card.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- trello

You can pass the configuration to the command with the --config argument:

steampipe_export_trello --config '<your_config>' trello_card