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.

​@Everton Colling thank you for your answer. 
Regarding indexing delay, the data was created/produced/edited days ago !

Expected timeseries have a truly None asset_id, as showed below.

The data_set_id is good, again as showed below :

from cognite.client.data_classes import filters
from cognite.client.data_classes.time_series import TimeSeriesProperty

external_id = "OPCUA-230115-i=6692"
dataset_id = 7443233575990547


asset_id_filter = filters.Not(filters.Exists(TimeSeriesProperty.asset_id))
dataset_filter = filters.Equals(TimeSeriesProperty.data_set_id, dataset_id)
advanced_filter = filters.And(
    asset_id_filter,
    dataset_filter
)

ts = client.time_series.list(
    limit=-1,
    advanced_filter=dataset_filter,
)
expected_ts = next((t for t in ts if t.external_id == external_id), None)
assert expected_ts is not None
print(expected_ts.external_id == external_id, expected_ts.asset_id is None)) # asset_id is truly None !

ts = client.time_series.filter(
    limit=-1,
    filter=asset_id_filter,
)
expected_ts = next((t for t in ts if t.external_id == external_id), None
assert expected_ts is not None, f"Timeseries with external_id {external_id} not found in filter results."
print(expected_ts.external_id == external_id, expected_ts.asset_id is None)

Output :

True True

AssertionError: Timeseries with external_id OPCUA-230115-i=6692 not found in filter results.

So despite expected_ts == None is True and the data_set_id are good, the list with filter on TimeSerieProperty.asset_id dit not include the expected TimeSeries.

Moreover you use the function .filter where I use .list with advanced_filter, is there a specific reason ?
The expected timeseries :
 

​@Everton Colling the raw API call did not find the TS. Thus, as you explained, the search index may be stale.

I will contact support as you advised. In any case, I'm not going crazy, which is a bit reassuring!
Thank you very much for your quick and well explained response. I will remember to post the support response!