rs_server_catalog/data_management/external_ids.md

<< Back to index

Search of catalog items by externalIds.

Items carry properties.externalIds as a list of {"scheme": ..., "value": ...} objects, possibly with other fields. A searched identifier is:

• scheme:value: an external identifier of the item has this scheme and this value,
• value or :value: an external identifier of the item has this value, whatever its scheme,
• scheme:: an external identifier of the item has this scheme, whatever its value.

Why a custom CQL2 operator

pgstac >= 0.10 cannot search inside a list of objects with the standard CQL2 operators: queryables only accept a JSON key path, and the array operators (a_overlaps...) compare whole elements. But each of the searches above is a jsonb containment, e.g. externalIds @> '[{"scheme": "prip"}]', that a GIN index serves efficiently.

pgstac translates the CQL2 operators that it doesn't know with the SQL templates of its pgstac.cql2_ops table. The catalog database therefore registers there the external_ids_contains operator (SQL_CREATE_EXTERNAL_IDS_OPERATOR), whose template is exactly the expression of the GIN index (SQL_CREATE_EXTERNAL_IDS_INDEXES), so that PostgreSQL uses the index:

{"op": "external_ids_contains", "args": [[{"scheme": "prip", "value": "U1"}]]}
-> (content->'properties'->'externalIds' @> to_text('[{"scheme": "prip", "value": "U1"}]')::jsonb)

The argument is a literal, quoted by pgstac (no SQL injection), and folded into a constant by PostgreSQL (to_text is immutable).

What the catalog does

Before forwarding a search to pgstac, the catalog replaces the externalIds search parameter and the externalIds conditions of the CQL2 filter by this operator (see translate_external_ids_filter). pgstac then evaluates the whole search: the SQL query has the size of the search, not of the number of matching items, and the pagination works as for any other search.

Searching items with any of several identifiers (the parameter, =, in, a_overlaps) gives one operator per identifier, combined with or; searching items with all of them (a_contains) gives one operator with all of them. Other uses of externalIds, which the index cannot serve, are refused (HTTP 400).

As for any property, an item without externalIds never matches a condition on them, even negated: NOT (externalIds = 'U1') only finds the items that have externalIds, none of them being U1.

Deployment

The operator and the indexes are created at deployment time, as the other pgstac customizations: keep this SQL aligned with rs-server-deployment (apps/01-cloudnative-pgstac) and rs-demo (local-mode/scripts/pgstac_post_actions.py).

bad_request(detail)

Return the HTTP 400 error raised for an externalIds search that is not supported.

Source code in docs/rs-server/services/catalog/rs_server_catalog/data_management/external_ids.py

def bad_request(detail: str) -> HTTPException:
    """Return the HTTP 400 error raised for an externalIds search that is not supported."""
    return HTTPException(status_code=HTTP_400_BAD_REQUEST, detail=detail)

external_ids_contains(terms)

Return the CQL2-JSON condition selecting the items whose externalIds contain all the terms.

Source code in docs/rs-server/services/catalog/rs_server_catalog/data_management/external_ids.py

def external_ids_contains(terms: Terms) -> dict:
    """Return the CQL2-JSON condition selecting the items whose externalIds contain all the terms."""
    return {"op": EXTERNAL_IDS_OPERATOR, "args": [terms]}

has_external_ids(node)

Check whether a CQL2-JSON filter references the externalIds property.

Source code in docs/rs-server/services/catalog/rs_server_catalog/data_management/external_ids.py

def has_external_ids(node: Any) -> bool:
    """Check whether a CQL2-JSON filter references the externalIds property."""
    if isinstance(node, dict):
        return node.get("property") == EXTERNAL_IDS_PROPERTY or any(has_external_ids(v) for v in node.values())
    if isinstance(node, list):
        return any(has_external_ids(v) for v in node)
    return False

parse_external_ids(raw)

Parse the externalIds searched by a client.

Accepted forms are scheme:value, :value and value (only the value must match) and scheme: (only the scheme must match), as a string, a comma-separated string or a list of them. The scheme is everything before the first colon, so a value containing colons without scheme is written :value.

Parameters:

Name	Type	Description	Default
raw	Any	the searched externalIds.	required

Returns:

Name	Type	Description
Terms	Terms	the distinct terms, in order of appearance.

Raises:

Type	Description
HTTPException	400 if there is no term, or a term has neither scheme nor value or is not a string.

Source code in docs/rs-server/services/catalog/rs_server_catalog/data_management/external_ids.py

def parse_external_ids(raw: Any) -> Terms:
    """
    Parse the externalIds searched by a client.

    Accepted forms are `scheme:value`, `:value` and `value` (only the value must
    match) and `scheme:` (only the scheme must match), as a string, a
    comma-separated string or a list of them. The scheme is everything before the
    first colon, so a value containing colons without scheme is written `:value`.

    Args:
        raw: the searched externalIds.

    Returns:
        Terms: the distinct terms, in order of appearance.

    Raises:
        HTTPException: 400 if there is no term, or a term has neither scheme nor value
            or is not a string.
    """
    terms: Terms = []
    for item in raw if isinstance(raw, list) else [raw]:
        if not isinstance(item, str):
            raise bad_request(f"Invalid externalIds value {item!r}: a string is expected.")
        for part in item.split(","):
            if not part.strip():
                continue
            scheme, colon, value = part.partition(":")
            scheme, value = (scheme.strip(), value.strip()) if colon else ("", scheme.strip())
            if not scheme and not value:
                raise bad_request(
                    f"Invalid externalIds value {part.strip()!r}: a scheme or a value is required "
                    "('scheme:value', 'value' or 'scheme:').",
                )
            term = {key: text for key, text in (("scheme", scheme), ("value", value)) if text}
            if term not in terms:
                terms.append(term)
    if not terms:
        raise bad_request("No externalIds identifier to search.")
    return terms

translate_external_ids_filter(node)

Replace the externalIds conditions of a CQL2-JSON filter by the external_ids_contains operator.

Supported conditions are =, in and a_overlaps (any of the ids) and a_contains (all of the ids), in any argument order, inside any and/or/not.

Parameters:

Name	Type	Description	Default
node	Any	the CQL2-JSON filter.	required

Returns:

Type	Description
Any	The filter, without any reference to the externalIds property.

Raises:

Type	Description
HTTPException	400 if an externalIds condition is not supported.

Source code in docs/rs-server/services/catalog/rs_server_catalog/data_management/external_ids.py

def translate_external_ids_filter(node: Any) -> Any:
    """
    Replace the externalIds conditions of a CQL2-JSON filter by the `external_ids_contains` operator.

    Supported conditions are `=`, `in` and `a_overlaps` (any of the ids) and
    `a_contains` (all of the ids), in any argument order, inside any and/or/not.

    Args:
        node: the CQL2-JSON filter.

    Returns:
        The filter, without any reference to the externalIds property.

    Raises:
        HTTPException: 400 if an externalIds condition is not supported.
    """
    if not has_external_ids(node):
        return node
    op = str(node.get("op", "")).lower() if isinstance(node, dict) else ""
    args = node.get("args") if isinstance(node, dict) else None
    if op in BOOLEAN_OPERATORS and isinstance(args, list):
        return {**node, "args": [translate_external_ids_filter(arg) for arg in args]}
    if op in ANY_OPERATORS + ALL_OPERATORS and isinstance(args, list) and len(args) == 2:
        for prop, values in (args, args[::-1]):
            if prop == {"property": EXTERNAL_IDS_PROPERTY} and not has_external_ids(values):
                terms = parse_external_ids(values)
                if op in ALL_OPERATORS:
                    return external_ids_contains(terms)
                conditions = [external_ids_contains([term]) for term in terms]
                return conditions[0] if len(conditions) == 1 else {"op": "or", "args": conditions}
    raise bad_request(
        f"Unsupported externalIds filter {json.dumps(node)}: externalIds can only be searched with "
        "'=', 'in', 'a_overlaps' or 'a_contains' and identifiers ('scheme:value', 'value' or 'scheme:').",
    )