How to represent MongoDb collection with nested JSON in Postgres using MONGO_FDW

Question

I'm not sure if I'm using the right terminology here so please bear with me.

Having got connectivity to a MongoDB from Postgres via Mongo_FDW, I now need to create the foreign tables in Postgres.

Taking one of the collections in MongoDB as example, running db.myCollection.findOne() gives:

{
  _id: ObjectId('65f2101ea863a99731ade050'),
  project_id: ObjectId('65f2101ca863a99731ade04f'),
  module_name: 'graph.txt',
  module_number: 1,
  filepath: 'test_projects/notes/graph.txt',
  created_at: { '$date': '2024-03-13T20:44:14.379Z' },
  code_chunk_count: null,
  code_chunks: 'Some Text)\n' +
    '#\n' +
    '# Some more text:\n' +
    '# Some more text:\n' +
    'Lots more text\n' +
    '   The End.\n',
  characters_count: 2093,
  module_costs: [
    {
      word: 51,
      spaces: 82,
      lines: 7,
      chars: 302
    }
  ],
  output: {
    low: '### Overview\n' +
      '\n' +
      'Loads of test\n' +
      '\n' +
      '### Dependencies\n' +
      '\n' +
      'Lots of lines \n' +
      'Tonnes of text.'
  }
}

My foreign table create is:

DROP FOREIGN TABLE myCollection;

CREATE FOREIGN TABLE myCollection
(
  _id INTEGER not null,
  project_id INTEGER,
  module_name VARCHAR(64),
  module_number INTEGER,
  filepath VARCHAR(128),
  created_at TIMESTAMP,
  code_chunk_count INTEGER,
  code_chunks text [],
  characters_count INTEGER,
  module_costs json,
  output json
)
SERVER "mon_db_svr"
OPTIONS (database 'mon_db', collection 'myCollection');


SELECT * FROM myCollection;

Output:

DROP FOREIGN TABLE
CREATE FOREIGN TABLE
psql: ERROR:  cannot convert BSON type to column type
HINT:  Column type: 1009

Is it possible instead just to have this as a single generic JSON type column containing all the data for each 'row'?

Answer 1

Sorry, I meant for this to be posted on "Database Admins" stackoverflow site.

Answer was to specify all the columns as JSON:

CREATE FOREIGN TABLE myCollection
(
  _id            json,
  project_id     json,
  module_name    json,
  module_number  json,
  filepath       json,
  created_at     json,
  code_chunk_count json,
  code_chunks      json,
  characters_count json,
  module_costs     json,
  output           json
)
SERVER "mon_db_svr"
OPTIONS (database 'mon_db', collection 'myCollection');