Advanced filter on timeseries for None value property

How about taking a slightly different approach: first retrieve all the time series, and then filter the results on the client side to keep only the rows where asset_id is null?
For example, something like the following idea

I hope this idea is helpful in some way.

Hello ​@Shun Takase ,

Thank you for your reply. It works very well, but that's not what I'm interested in: I want the optimal solution!

In your solution, I have to unnecessarily query all timeseries, even though only a minority of them are eligible.

I would like a completely server-side solution, so my question extends to the other attributes of a timeseries that can be set to None!

Regards

Hi ​@RAMBOURG Pierre

filters.Not(filters.Exists(TimeSeriesProperty.asset_id)) is the correct approach and it does work. I've verified this with a minimal deterministic test. Here's a self-contained script you can run on your end to confirm:

"""
Minimal test: filter time series where asset_id is None.
Creates 3 time series (1 with asset, 2 without), filters, then cleans up.
"""
import time
from cognite.client import CogniteClient
from cognite.client.data_classes import Asset, TimeSeries, filters
from cognite.client.data_classes.time_series import TimeSeriesProperty

client = CogniteClient()  # uses your default credentials

PREFIX = "test-null-assetid"
asset = None
created_ts = []

try:
    # Setup: create one asset and three time series
    asset = client.assets.create(Asset(external_id=f"{PREFIX}-asset", name="Test Asset"))

    created_ts = client.time_series.create([
        TimeSeries(external_id=f"{PREFIX}-with-asset", name="Has asset", asset_id=asset.id),
        TimeSeries(external_id=f"{PREFIX}-no-asset-1", name="No asset 1"),
        TimeSeries(external_id=f"{PREFIX}-no-asset-2", name="No asset 2"),
    ])

    # Wait for the advancedFilter index to catch up
    for i in range(15):
        time.sleep(2)
        all_ts = client.time_series.filter(
            filters.Prefix("external_id", PREFIX), limit=-1
        )
        if len(all_ts) == 3:
            break

    # Filter: time series WITHOUT asset_id
    no_asset = client.time_series.filter(
        filters.And(
            filters.Prefix("external_id", PREFIX),
            filters.Not(filters.Exists(TimeSeriesProperty.asset_id)),
        ),
        limit=-1,
    )
    print(f"Without asset_id: {len(no_asset)} (expected 2)")
    for ts in no_asset:
        print(f"  {ts.external_id}  asset_id={ts.asset_id}")

    # Filter: time series WITH asset_id
    has_asset = client.time_series.filter(
        filters.And(
            filters.Prefix("external_id", PREFIX),
            filters.Exists(TimeSeriesProperty.asset_id),
        ),
        limit=-1,
    )
    print(f"With asset_id:    {len(has_asset)} (expected 1)")
    for ts in has_asset:
        print(f"  {ts.external_id}  asset_id={ts.asset_id}")

finally:
    # Cleanup
    client.time_series.delete(
        external_id=[ts.external_id for ts in created_ts], ignore_unknown_ids=True
    )
    if asset:
        client.assets.delete(id=asset.id, ignore_unknown_ids=True)
    print("\nCleaned up all test resources.")

Expected output:

Without asset_id: 2 (expected 2)
  test-null-assetid-no-asset-1  asset_id=None
  test-null-assetid-no-asset-2  asset_id=None
With asset_id:    1 (expected 1)
  test-null-assetid-with-asset  asset_id=...

A few things to check on your side if you're still seeing 0 results:

1. Indexing delay: the advancedFilter uses a search index that may take a few seconds to update after time series are created or modified. The wait loop above handles this, but if you removed an asset link from existing time series, you may need to wait before the index reflects that change.
2. Verify asset_id is truly None: retrieve the time series directly with client.time_series.retrieve(external_id="foobar") and check .asset_id. It's possible the time series still has an asset reference that you're not expecting.
3. Check the data_set_id scope: your original filter combines data_set_id + Not(Exists(asset_id)). Make sure the time series with null asset_id are actually in that data set.