You can find more examples in the Python API documentation, many which are applicable to other clients as well.
Filter out objects based on child tasks
This query will filter out Shot objects where there are two child tasks, one named foo with status Not started and the other named baz with status In progress.
Shot where children[Task] any (name is "foo" and status.name is "Not started") and children[Task] any (name is "baz" and status.name is "In progress")
The Shot object is a custom object in ftrack and we use the any operator to filter on multiple criteria in the children relation. The [Task] syntax is used to specify that we are only interested in child objects that are of type Task.
Query all assets in a project
This query will filter out all AssetVersions published to a particular project.
AssetVersion where asset.parent.project.name is "project_code" or parent.name is "project_code"
Query all asset versions that have changed status between two dates
Status changes for asset versions and tasks are traced historically, this allows for queries that look for changes to the status over time. Below we filter out all asset versions that have not had there status changed between the dates 2018-01-08 and 2018-01-15.
AssetVersion where status_changes any (date > 2018-01-08) and status_changes any (date < 2018-01-15 )