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 :
 

Hi ​@RAMBOURG Pierre!

thank you for the detailed reproduction, that helps to understand the issue you are facing.

I had a look at the SDK code and filter() and list(advanced_filter=...) are functionally equivalent, as they produce identical API calls under the hood. The only difference is that filter() is deprecated and will be removed in a future SDK version, so I'd recommend switching to list(advanced_filter=...) going forward. But this is not causing your issue.

I extended my test covering all three approaches:

• SDK filter()
• SDK list(advanced_filter=...)
• raw API POST /timeseries/list 

I also tested the scenario of updating a time series to remove its asset_id. All cases work as expected on my project, including the Not(Exists(asset_id)) filter correctly finding time series with null asset_id.

Given your evidence, with asset_id confirmed to be None via both retrieve() and the UI, created_time == last_updated_time (never modified), and the TS is found by the dataset filter but not by Not(Exists(asset_id)), this points to a server-side search index inconsistency for specific time series in your project.

You can confirm this is a server-side issue (not SDK) by running the equivalent raw API call:

resp = client.post(
    url=f"/api/v1/projects/{client.config.project}/timeseries/list",
    json={
        "advancedFilter": {
            "and": [
                {"equals": {"property": ["dataSetId"], "value": 7443233575990547}},
                {"not": {"exists": {"property": ["assetId"]}}},
            ]
        },
        "limit": 100,
    },
)
items = resp.json()["items"]
found = any(i["externalId"] == "OPCUA-230115-i=6692" for i in items)
print(f"Found via raw API: {found}")

If this also returns False, it confirms the search index is stale for this TS and I'd recommend filing a support ticket with Cognite, referencing time series ID 5064386156929082 and dataset 7443233575990547. The support team will have to investigate.