def _build_metadata_operator_condition(
    relative_path: str,
    op: str,
    value: Any,
    param_helper: ParamHelper,
    json_column: str,
) -> str:
    """Builds the specific SQL for an operator on a JSONB path."""

    path_parts = relative_path.split(".")

    # Determine accessors WITH and WITHOUT text extraction
    if len(path_parts) == 1:
        quoted_key = f"'{path_parts[0]}'"
        json_accessor_text = f"{json_column} ->> {quoted_key}"
        json_accessor_jsonb = f"{json_column} -> {quoted_key}"
    else:
        quoted_path_parts = [f'"{p}"' for p in path_parts]
        path_literal = "'{" + ",".join(quoted_path_parts) + "}'"
        json_accessor_text = f"{json_column} #>> {path_literal}"
        json_accessor_jsonb = f"{json_column} #> {path_literal}"

    # --- JSONB Specific Operators (?|, @>) ---

    if op == FilterOperator.IN:
        if not isinstance(value, list):
            raise FilterError(
                f"'{op}' requires list value for '{relative_path}'."
            )
        if not value:
            return "FALSE"
        try:
            str_values = [str(item) for item in value]
            array_literal = _build_array_literal(
                str_values, param_helper, "text"
            )
            # REMOVED extra parentheses around accessor
            return f"{json_accessor_jsonb} ?| {array_literal}"
        except Exception as e:
            raise FilterError(
                f"Error processing values for '{op}' on '{relative_path}': {e}"
            ) from e

    elif op == FilterOperator.NIN:
        if not isinstance(value, list):
            raise FilterError(
                f"'{op}' requires list value for '{relative_path}'."
            )
        if not value:
            return "TRUE"
        try:
            str_values = [str(item) for item in value]
            array_literal = _build_array_literal(
                str_values, param_helper, "text"
            )
            # REMOVED extra parentheses around accessor inside NOT()
            return f"NOT ({json_accessor_jsonb} ?| {array_literal})"
        except Exception as e:
            raise FilterError(
                f"Error processing values for '{op}' on '{relative_path}': {e}"
            ) from e

    elif op == FilterOperator.JSON_CONTAINS:
        try:
            json_value_str = json.dumps(value)
            placeholder = param_helper.add(json_value_str)
            # REMOVED extra parentheses around accessor
            return f"{json_accessor_jsonb} @> {placeholder}::jsonb"
        except TypeError as e:
            raise FilterError(
                f"Value for '{op}' on '{relative_path}' must be JSON serializable: {e}"
            ) from e

    elif (
        op == FilterOperator.ARRAY_CONTAINS
    ):  # This is equivalent to "$contains"
        if not isinstance(value, list):
            raise FilterError(
                f"Operator '{op}' on JSONB path '{relative_path}' requires a list value (representing elements to check for containment)."
            )
        if not value:
            # Containing all elements of an empty set is usually true
            return "TRUE"
        try:
            # Convert the list of values into a JSONB array literal for the @> operator
            json_array_value = json.dumps(value)
            placeholder = param_helper.add(json_array_value)
            # Use the @> operator: checks if the left JSONB (the target array)
            # contains the right JSONB (the array of elements we're looking for)
            return f"{json_accessor_jsonb} @> {placeholder}::jsonb"
        except TypeError as e:
            raise FilterError(
                f"Value for '{op}' on '{relative_path}' must be JSON serializable: {e}"
            ) from e
        except Exception as e:
            raise FilterError(
                f"Error processing values for '{op}' on '{relative_path}': {e}"
            ) from e

    # --- Standard comparisons (operating on text extraction ->> or #>>) ---

    # Handle NULL comparisons
    if value is None:
        if op == FilterOperator.EQ:
            return f"{json_accessor_text} IS NULL"
        elif op == FilterOperator.NE:
            return f"{json_accessor_text} IS NOT NULL"
        else:
            return "FALSE"

    # --- Standard Scalar Comparisons ---
    sql_op_map = {
        FilterOperator.EQ: "=",
        FilterOperator.NE: "!=",
        FilterOperator.LT: "<",
        FilterOperator.LTE: "<=",
        FilterOperator.GT: ">",
        FilterOperator.GTE: ">=",
    }

    if op in sql_op_map:
        sql_operator = sql_op_map[op]
        if isinstance(value, bool):
            placeholder = param_helper.add(value)
            # Keep safety checks - tests will be updated
            return f"({json_accessor_text} IS NOT NULL AND ({json_accessor_text})::boolean {sql_operator} {placeholder})"
        elif isinstance(value, (int, float)):
            placeholder = param_helper.add(value)
            # Keep safety checks - tests will be updated
            # Ensure public.is_numeric function exists in your DB!
            return f"({json_accessor_text} IS NOT NULL AND ({json_accessor_text})::numeric {sql_operator} {placeholder})"
        elif isinstance(value, str):
            placeholder = param_helper.add(value)
            # Direct text comparison needs no extra checks usually
            return f"{json_accessor_text} {sql_operator} {placeholder}"
        else:
            placeholder = param_helper.add(str(value))
            return f"{json_accessor_text} {sql_operator} {placeholder}"

    # --- String Like ---
    elif op == FilterOperator.LIKE:
        if not isinstance(value, str):
            raise FilterError(
                f"'{op}' requires string value for '{relative_path}'."
            )
        placeholder = param_helper.add(value)
        return f"{json_accessor_text} LIKE {placeholder}"
    elif op == FilterOperator.ILIKE:
        if not isinstance(value, str):
            raise FilterError(
                f"'{op}' requires string value for '{relative_path}'."
            )
        placeholder = param_helper.add(value)
        return f"{json_accessor_text} ILIKE {placeholder}"

    # --- Fallback IN / NIN (operating on text extraction) ---
    elif op == FilterOperator.IN:
        if not isinstance(value, list):
            raise FilterError(
                f"Fallback '{op}' requires list value for '{relative_path}'."
            )
        if not value:
            return "FALSE"
        placeholders = [param_helper.add(str(item)) for item in value]
        # Standard SQL IN needs parentheses around the accessor
        return f"({json_accessor_text}) IN ({', '.join(placeholders)})"

    elif op == FilterOperator.NIN:
        if not isinstance(value, list):
            raise FilterError(
                f"Fallback '{op}' requires list value for '{relative_path}'."
            )
        if not value:
            return "TRUE"
        placeholders = [param_helper.add(str(item)) for item in value]
        # Standard SQL NOT IN needs parentheses around the accessor
        return f"({json_accessor_text}) NOT IN ({', '.join(placeholders)})"

    # --- Operator Not Handled ---
    else:
        raise FilterError(
            f"Unsupported operator '{op}' for metadata field '{relative_path}'."
        )
Builds the specific SQL for an operator on a JSONB path.
py/core/providers/database/filters.py