Basic DQL Definition

DQL Query

The following DQL attributes can be used in queries to filter and sort data. To learn more about the item JSON fields that the DQL queries, click here.

resource

Specifies the resource to be queried.
"resource": "items" | "annotations"

filter

Specifies the filter to be applied, and may include a combination of field values and operators.
"filter": {...},

For example, to get all items named “foo” with extension “bar” in the dataset root folder, run this query in the DQL Editor:

{
    "filter": {
        "$and":[{
            "hidden":false
        }, {
            "filename": {
                "$eq":"/foo.bar"
            }}, {
            "type":"file"
        }]}}

join

[optional field]
Filters a resource by another resource, for example, items by the items' annotations.
"join": { "filter": {...},
"on": {...}},

For example, if you filter items in the dataset by annotation type box, you will see the DQL Editor produce the following query:

{
    "filter": {
        "$and":[{
            "hidden":false
        }, {
            "type":"file"
        }]},"join": {
        "filter": {
            "$and":[{
                "type": {
                    "$in":["box"]}}]},"on": {
            "resource":"annotations","local":"itemId","forigen":"id"
        }}}

The field "local" is the key on the joined resource (e.g., annotations).
The field "forigen" is the key on the returned resource (e.g., items).

DQL in the SDK

To learn more about filtering and DQL queries in the SDK, click here.

1. Filter: Get all items named foo.bar

import dtlpy as dl
if dl.token_expired():
    dl.login()
project = dl.projects.get(project_name='project_name')
dataset = project.datasets.get(dataset_name='dataset_name')
# Create filters instance
filters = dl.Filters()
filters.add(field='name', values='foo.bar.*')
# Get filtered item list in a page object
pages = dataset.items.list(filters=filters)
# Count the items
print('Number of filtered items in dataset: {}'.format(pages.items_count))
# Iterate through the items - go over all items and print the properties
for page in pages:
    for item in page:
        item.print()

2. Join: get items that have box annotations

import dtlpy as dl
if dl.token_expired():
    dl.login()
project = dl.projects.get(project_name='project_name')
dataset = project.datasets.get(dataset_name='dataset_name')
# Create filters instance
filters = dl.Filters()
filters.add_join(field='type', values='box')
# Get filtered item list in a page object
pages = dataset.items.list(filters=filters)
# Count the items
print('Number of filtered items in dataset: {}'.format(pages.items_count))
# Iterate through the items - go over all items and print the properties
for page in pages:
    for item in page:
        item.print()

3. Sort: Sort files of size 0-5 MB by name, in ascending order

import dtlpy as dl
if dl.token_expired():
    dl.login()
project = dl.projects.get(project_name='project_name')
dataset = project.datasets.get(dataset_name='dataset_name')
# Create filters instance
filters = dl.Filters()
filters.add(field='metadata.system.size', values='0', operator='gt')
filters.add(field='metadata.system.size', values='5242880', operator='lt')
filters.sort_by(field=dl.FILTERS_KNOWN_FIELDS_FILENAME, value=dl.FILTERS_ORDERBY_DIRECTION_ASCENDING)
# Get filtered item list in a page object
pages = dataset.items.list(filters=filters)
# Count the items
print('Number of filtered items in dataset: {}'.format(pages.items_count))
# Iterate through the items - go over all items and print the properties
for page in pages:
    for item in page:
        item.print()

4. Sort with multiple fields: Sort by labels ascending and createdAt descending

import dtlpy as dl
if dl.token_expired():
    dl.login()
project = dl.projects.get(project_name='project_name')
dataset = project.datasets.get(dataset_name='dataset_name')
# Create filters instance
filters = dl.Filters()
# set annotation resource
filters.resource = dl.FiltersResource.ANNOTATION
# return results sorted by descending label
filters.sort_by(field=dl.FILTERS_KNOWN_FIELDS_LABEL, value=dl.FILTERS_ORDERBY_DIRECTION_ASCENDING)
filters.sort_by(field=dl.FILTERS_KNOWN_FIELDS_CREATED_AT, value=dl.FILTERS_ORDERBY_DIRECTION_DESCENDING)
# Get filtered item list in a page object
pages = dataset.items.list(filters=filters)
# Count the items
print('Number of filtered items in dataset: {}'.format(pages.items_count))
# Iterate through the items - go over all items and print the properties
for page in pages:
    for item in page:
        item.print()

5. Items With No Attributes

To select all items with no attributes, edit the DQL query to include an empty array.

"attributes": {}

    "filter": {
        "$and":[{
            "hidden":false
        },
            {
                "type":"file"
            }]},
    "join": {
        "filter": {
            "$and":[{
                "$or":[{
                    "$or":[{
                        "metadata": {
                            "system": {
                                "attributes": {}
         }}}]}]}]},
        "on": {
            "resource":"annotations",
            "local":"itemId",
            "forigen":"id"
        }}}

6. Item Metadata Example

The following is an example of the user-metadata field 'details':

{
	"system": {
		"originalname": "name2.png",
		"size": 675834,
		"encoding": "7bit",
		"mimetype": "image/png",
		"annotationStatus": []
	},
	"details": {
		"car-color": "blue"
	}
}

Response to DQL Query

A typical response to a DQL query will look like the following:

{
    "totalItemsCount": number,
    "items": Array,
    "totalPagesCount": number,
    "hasNextPage": boolean,
}

# A possible result:

{
    "totalItemsCount": 2,
    "totalPagesCount": 1,
    "hasNextPage": false,
    "items": [
        {
            "id": "5d0783852dbc15306a59ef6c",
            "createdAt": "2019-06-18T23:29:15.775Z",
            "filename": "/5546670769_8df950c6b6.jpg",
            "type": "file"
            // ...
        },
        {
            "id": "5d0783852dbc15306a59ef6d",
            "createdAt": "2019-06-19T23:29:15.775Z",
            "filename": "/5551018983_3ce908ac98.jpg",
            "type": "file"
            // ...
        }
    ]
}