Mongo Connector Fails in loading any data due to unserializable Objectid property

[clearnote01]

DataSource: Mongo-db
Query:

{
	"collection": "<collection-name-here>",
        "limit": 10
}

Stacktrace

redash-worker-1     | [2024-02-01 13:41:19,385][PID:39][INFO][rq.job.redash.tasks.queries.execution] job.func_name=redash.tasks.queries.execution.execute_query job.id=13217596-2b43-4e39-a6a9-970803b97469 job=execute_query query_hash=834605732f3a68d75d594e33921af383 ds_id=4 data_length=2 error=[None]
redash-worker-1     | [2024-02-01 13:41:19,389][PID:39][INFO][root] Inserted query (834605732f3a68d75d594e33921af383) data; id=None
redash-worker-1     | [2024-02-01 13:41:19,413][PID:39][ERROR][rq.worker] Traceback (most recent call last):
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1204, in _execute_context
redash-worker-1     |     context = constructor(dialect, self, conn, *args)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 873, in _init_compiled
redash-worker-1     |     param = dict(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 876, in <genexpr>
redash-worker-1     |     processors[key](compiled_params[key])
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 1232, in process
redash-worker-1     |     return process_param(value, dialect)
redash-worker-1     |   File "/app/redash/models/types.py", line 29, in process_bind_param
redash-worker-1     |     return json_dumps(value)
redash-worker-1     |   File "/app/redash/utils/__init__.py", line 132, in json_dumps
redash-worker-1     |     return json.dumps(data, *args, **kwargs)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/__init__.py", line 234, in dumps
redash-worker-1     |     return cls(
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 199, in encode
redash-worker-1     |     chunks = self.iterencode(o, _one_shot=True)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 257, in iterencode
redash-worker-1     |     return _iterencode(o, 0)
redash-worker-1     |   File "/app/redash/utils/__init__.py", line 114, in default
redash-worker-1     |     result = super(JSONEncoder, self).default(o)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 179, in default
redash-worker-1     |     raise TypeError(f'Object of type {o.__class__.__name__} '
redash-worker-1     | TypeError: Object of type ObjectId is not JSON serializable
redash-worker-1     | 
redash-worker-1     | The above exception was the direct cause of the following exception:
redash-worker-1     | 
redash-worker-1     | Traceback (most recent call last):
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/rq/worker.py", line 1031, in perform_job
redash-worker-1     |     rv = job.perform()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/rq/job.py", line 749, in perform
redash-worker-1     |     self._result = self._execute()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/rq/job.py", line 772, in _execute
redash-worker-1     |     result = self.func(*self.args, **self.kwargs)
redash-worker-1     |   File "/app/redash/tasks/queries/execution.py", line 276, in execute_query
redash-worker-1     |     return QueryExecutor(
redash-worker-1     |   File "/app/redash/tasks/queries/execution.py", line 227, in run
redash-worker-1     |     updated_query_ids = models.Query.update_latest_result(query_result)
redash-worker-1     |   File "/app/redash/models/__init__.py", line 727, in update_latest_result
redash-worker-1     |     for q in queries:
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3534, in __iter__
redash-worker-1     |     self.session._autoflush()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1633, in _autoflush
redash-worker-1     |     util.raise_(e, with_traceback=sys.exc_info()[2])
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
redash-worker-1     |     raise exception
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1622, in _autoflush
redash-worker-1     |     self.flush()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
redash-worker-1     |     self._flush(objects)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
redash-worker-1     |     transaction.rollback(_capture_exception=True)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
redash-worker-1     |     compat.raise_(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
redash-worker-1     |     raise exception
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
redash-worker-1     |     flush_context.execute()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
redash-worker-1     |     rec.execute(self)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
redash-worker-1     |     persistence.save_obj(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
redash-worker-1     |     _emit_insert_statements(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
redash-worker-1     |     result = cached_connections[connection].execute(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
redash-worker-1     |     return meth(self, multiparams, params)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
redash-worker-1     |     return connection._execute_clauseelement(self, multiparams, params)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
redash-worker-1     |     ret = self._execute_context(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1206, in _execute_context
redash-worker-1     |     self._handle_dbapi_exception(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
redash-worker-1     |     util.raise_(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
redash-worker-1     |     raise exception
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1204, in _execute_context
redash-worker-1     |     context = constructor(dialect, self, conn, *args)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 873, in _init_compiled
redash-worker-1     |     param = dict(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 876, in <genexpr>
redash-worker-1     |     processors[key](compiled_params[key])
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 1232, in process
redash-worker-1     |     return process_param(value, dialect)
redash-worker-1     |   File "/app/redash/models/types.py", line 29, in process_bind_param
redash-worker-1     |     return json_dumps(value)
redash-worker-1     |   File "/app/redash/utils/__init__.py", line 132, in json_dumps
redash-worker-1     |     return json.dumps(data, *args, **kwargs)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/__init__.py", line 234, in dumps
redash-worker-1     |     return cls(
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 199, in encode
redash-worker-1     |     chunks = self.iterencode(o, _one_shot=True)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 257, in iterencode
redash-worker-1     |     return _iterencode(o, 0)
redash-worker-1     |   File "/app/redash/utils/__init__.py", line 114, in default
redash-worker-1     |     result = super(JSONEncoder, self).default(o)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 179, in default
redash-worker-1     |     raise TypeError(f'Object of type {o.__class__.__name__} '
redash-worker-1     | sqlalchemy.exc.StatementError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
redash-worker-1     | (builtins.TypeError) Object of type ObjectId is not JSON serializable
redash-worker-1     | [SQL: INSERT INTO query_results (org_id, data_source_id, query_hash, query, data, runtime, retrieved_at) VALUES (%(org_id)s, %(data_source_id)s, %(query_hash)s, %(query)s, %(data)s, %(runtime)s, %(retrieved_at)s) RETURNING query_results.id]
redash-worker-1     | REDACTED
redash-worker-1     | Traceback (most recent call last):
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1204, in _execute_context
redash-worker-1     |     context = constructor(dialect, self, conn, *args)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 873, in _init_compiled
redash-worker-1     |     param = dict(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 876, in <genexpr>
redash-worker-1     |     processors[key](compiled_params[key])
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 1232, in process
redash-worker-1     |     return process_param(value, dialect)
redash-worker-1     |   File "/app/redash/models/types.py", line 29, in process_bind_param
redash-worker-1     |     return json_dumps(value)
redash-worker-1     |   File "/app/redash/utils/__init__.py", line 132, in json_dumps
redash-worker-1     |     return json.dumps(data, *args, **kwargs)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/__init__.py", line 234, in dumps
redash-worker-1     |     return cls(
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 199, in encode
redash-worker-1     |     chunks = self.iterencode(o, _one_shot=True)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 257, in iterencode
redash-worker-1     |     return _iterencode(o, 0)
redash-worker-1     |   File "/app/redash/utils/__init__.py", line 114, in default
redash-worker-1     |     result = super(JSONEncoder, self).default(o)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 179, in default
redash-worker-1     |     raise TypeError(f'Object of type {o.__class__.__name__} '
redash-worker-1     | TypeError: Object of type ObjectId is not JSON serializable
redash-worker-1     | 
redash-worker-1     | The above exception was the direct cause of the following exception:
redash-worker-1     | 
redash-worker-1     | Traceback (most recent call last):
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/rq/worker.py", line 1031, in perform_job
redash-worker-1     |     rv = job.perform()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/rq/job.py", line 749, in perform
redash-worker-1     |     self._result = self._execute()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/rq/job.py", line 772, in _execute
redash-worker-1     |     result = self.func(*self.args, **self.kwargs)
redash-worker-1     |   File "/app/redash/tasks/queries/execution.py", line 276, in execute_query
redash-worker-1     |     return QueryExecutor(
redash-worker-1     |   File "/app/redash/tasks/queries/execution.py", line 227, in run
redash-worker-1     |     updated_query_ids = models.Query.update_latest_result(query_result)
redash-worker-1     |   File "/app/redash/models/__init__.py", line 727, in update_latest_result
redash-worker-1     |     for q in queries:
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3534, in __iter__
redash-worker-1     |     self.session._autoflush()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1633, in _autoflush
redash-worker-1     |     util.raise_(e, with_traceback=sys.exc_info()[2])
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
redash-worker-1     |     raise exception
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1622, in _autoflush
redash-worker-1     |     self.flush()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
redash-worker-1     |     self._flush(objects)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
redash-worker-1     |     transaction.rollback(_capture_exception=True)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
redash-worker-1     |     compat.raise_(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
redash-worker-1     |     raise exception
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
redash-worker-1     |     flush_context.execute()
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
redash-worker-1     |     rec.execute(self)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
redash-worker-1     |     persistence.save_obj(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
redash-worker-1     |     _emit_insert_statements(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
redash-worker-1     |     result = cached_connections[connection].execute(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
redash-worker-1     |     return meth(self, multiparams, params)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
redash-worker-1     |     return connection._execute_clauseelement(self, multiparams, params)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
redash-worker-1     |     ret = self._execute_context(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1206, in _execute_context
redash-worker-1     |     self._handle_dbapi_exception(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
redash-worker-1     |     util.raise_(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
redash-worker-1     |     raise exception
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1204, in _execute_context
redash-worker-1     |     context = constructor(dialect, self, conn, *args)
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 873, in _init_compiled
redash-worker-1     |     param = dict(
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 876, in <genexpr>
redash-worker-1     |     processors[key](compiled_params[key])
redash-worker-1     |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 1232, in process
redash-worker-1     |     return process_param(value, dialect)
redash-worker-1     |   File "/app/redash/models/types.py", line 29, in process_bind_param
redash-worker-1     |     return json_dumps(value)
redash-worker-1     |   File "/app/redash/utils/__init__.py", line 132, in json_dumps
redash-worker-1     |     return json.dumps(data, *args, **kwargs)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/__init__.py", line 234, in dumps
redash-worker-1     |     return cls(
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 199, in encode
redash-worker-1     |     chunks = self.iterencode(o, _one_shot=True)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 257, in iterencode
redash-worker-1     |     return _iterencode(o, 0)
redash-worker-1     |   File "/app/redash/utils/__init__.py", line 114, in default
redash-worker-1     |     result = super(JSONEncoder, self).default(o)
redash-worker-1     |   File "/usr/local/lib/python3.8/json/encoder.py", line 179, in default
redash-worker-1     |     raise TypeError(f'Object of type {o.__class__.__name__} '
redash-worker-1     | sqlalchemy.exc.StatementError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
redash-worker-1     | (builtins.TypeError) Object of type ObjectId is not JSON serializable

One note is that I was able to fix this by modifying the run_query method of MongoDB query running by adding following line before returning data object from the function. Looking at the json_encoder defined here, it seems like that should have taken care of stringifying the ObjectId field properly, so I'm not sure why the encoding broke.

// my fix
data = json_loads(bson.json_util.dumps(data))

Technical details:
Redash Version: master branch
Browser/OS: Chrome / Docker
How did you install Redash: local development setup guide

[MemberIT]

I confirm, I have the same problem. I had to roll back redash to a commit 998dc31 and revert the migrations:

/app/manage.py db downgrade 7ce5925f832b

@AndrewChubatiuk, please pay attention to this issue.
I also think that there could be similar problems with queries to postgresql.

[guidopetri]

@MemberIT / @clearnote01 could you try the preview image and see if the change in #6741 fixed your problem?

[clearnote01]

@guidopetri Yes, this issue is fixed for me now. Thank you. I have another problem with columns being duplicated for mongdob connector, but seems like there is an open issue/fix for it: #6641