Field Lookups

Field lookups are keyword arguments passed to filter(), exclude(), get(), and Q(). The syntax is field__lookup=value.

Person.objects.filter(age__lt=18)
#                     ^^^  ^^
#                     field lookup

If no lookup is specified, eq (exact match) is used by default:

Person.objects.filter(name='John')
# equivalent to:
Person.objects.filter(name__eq='John')

Comparison Lookups

Lookup	SQL	Example
eq	=	age__eq=18
neq	!=	gender__neq='male'
gt	>	age__gt=18
gte	>=	age__gte=18
lt	<	age__lt=18
lte	<=	age__lte=18
in	IN	age__in=[20, 30, 40]
isnull	IS NULL / IS NOT NULL	email__isnull=True

String Lookups

Lookup	Case-sensitive	Description
contains	Yes	name__contains='har' — matches "Charles", not "Harry"
icontains	No	name__icontains='har' — matches "Charles" and "Harry"
startswith	Yes	name__startswith='Jo'
istartswith	No	name__istartswith='jo'
endswith	Yes	name__endswith='son'
iendswith	No	name__iendswith='SON'

Nested Field Lookups

Any lookup that doesn't match a known lookup type is treated as a JSON nested field lookup:

SyncAsync

Person.objects.filter(json_field__nested_field__eq='value').execute()

await Person.objects.filter(json_field__nested_field__eq='value').aexecute()

Filtering by Related Object Fields

Use select_related first, then filter by related fields using __ notation:

SyncAsync

Person.objects.select_related('company').filter(company__name='Acme').execute()

await Person.objects.select_related('company').filter(company__name='Acme').aexecute()

This works with multiple levels of nesting:

Person.objects.select_related(
    'company',
    'company__location',
).filter(company__location__name='New York')

Metadata Lookups

Use the _metadata prefix to filter by Metadata fields:

Field	Type	Description
is_deleted	bool	Whether the object is soft-deleted
created_at	int	Creation timestamp (ms since epoch)
updated_at	int	Last update timestamp (ms since epoch)
object_id	Any	Object identifier
object_version	str \| Versions	Object version
prior_version	str \| None	Previous version identifier

# Deleted records
Person.objects.filter(_metadata__is_deleted=True)

SyncAsync

from datetime import datetime, timedelta

# Count records created in the last 24 hours
result = Person.objects.filter(
    _metadata__created_at__gt=datetime.now() - timedelta(hours=24),
).count().execute()

from datetime import datetime, timedelta

result = await Person.objects.filter(
    _metadata__created_at__gt=datetime.now() - timedelta(hours=24),
).count().aexecute()

Address Lookups

Use the _address prefix to filter by object identity and version:

• _address__object_id — unique object identifier
• _address__object_version — specific version or Versions.ALL / Versions.LATEST
• _address__class_version — schema version

Note

Address lookups require a historical connection (including lakehouse).

SyncAsync

from amsdal import Versions

# Get all versions of a specific object
result = Person.objects.filter(
    _address__object_id='12345',
    _address__object_version=Versions.ALL,
    _address__class_version=Versions.ALL,
).execute()

from amsdal import Versions

result = await Person.objects.filter(
    _address__object_id='12345',
    _address__object_version=Versions.ALL,
    _address__class_version=Versions.ALL,
).aexecute()

Filtering by Objects

Pass a model instance directly to filter by its reference:

SyncAsync

new_york = Location.objects.get(name='New York').execute()
result = Person.objects.filter(location=new_york).execute()

new_york = await Location.objects.get(name='New York').aexecute()
result = await Person.objects.filter(location=new_york).aexecute()

You can also filter by a Reference object directly:

SyncAsync

ref = new_york.build_reference()
result = Person.objects.filter(location=ref).execute()

ref = await new_york.abuild_reference()
result = await Person.objects.filter(location=ref).aexecute()