[🐛 Bug]: SQLAlchemy UndefinedFunction in get_alerts_by_fingerprint

[diegovb-sys]

Describe the bug
Using Postgres as db engine, and calling get_alerts_by_fingerprint with a status != None.

if status:
            query = query.filter(func.json_extract(Alert.event, "$.status") == status)

I get the following error message:

File \"/venv/lib/python3.13/site-packages/sqlalchemy/engine/default.py\", line 941, in do_execute\n cursor.execute(statement, parameters)\n ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^\npsycopg2.errors.UndefinedFunction: function json_extract(json, unknown) does not exist\

File \"/venv/lib/python3.13/site-packages/sqlalchemy/engine/default.py\", line 941, in do_execute\n cursor.execute(statement, parameters)\n ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^\nsqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function json_extract(json, unknown) does not exist\nLINE 3: ...f8cc44d184b341be7a46e7fc11f6c065a59545cd0d84' AND json_extra...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.

Therefore, this call is not agnostic with respect to the database engine.

Additional context
In order to fix it, it should be swap by:

def get_json_extract_field(session, base_field, key):
    if session.bind.dialect.name == "postgresql":
        return func.json_extract_path_text(base_field, key)
    elif session.bind.dialect.name == "mysql":
        return func.json_unquote(func.json_extract(base_field, "$.{}".format(key)))
    else:
        return func.json_extract(base_field, "$.{}".format(key))

Moreover, it must be taking into account that this behavior is not isolate. There are a few of functions with the same dependency of the db engine.

[dosubot]

The error happens because PostgreSQL doesn't support the json_extract function—it's specific to MySQL/SQLite. PostgreSQL requires different JSON extraction methods, like ->> or json_extract_path_text. The current code in get_alerts_by_fingerprint always uses func.json_extract, which causes this compatibility issue and breaks on Postgres backends.

A dialect-aware approach is needed. The recommended fix is to check the database dialect and use Alert.event.op('->>')('status') for PostgreSQL, and func.json_extract for other databases. This pattern has been discussed and implemented in the community, and it resolves the error, making the code database-agnostic. For example:

if status:
    if session.bind.dialect.name == "postgresql":
        query = query.filter(Alert.event.op("->>")("status") == status)
    else:
        query = query.filter(func.json_extract(Alert.event, "$.status") == status)

(source)

This issue is not isolated—other functions in the codebase also use func.json_extract or similar backend-specific SQL functions without abstraction, so a helper function (like the one you suggested) should be implemented and adopted throughout the codebase for maintainability and cross-database compatibility.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}