Anyone have any thoughts on whether or not I can deal with this through Meltano or if I need to do something in the database. I have a collection that has records with very different structures. I'm working with the tap-mongodb.

[
  {
    "_id": "6013a71228304797e19c12eb",
    "identifier": "usertype",
    "value": [
      "Base",
      "Admin",
    ]
  },
  {
    "_id": "6013a71228304797e19c12ec",
    "identifier": "status",
    "value": [
      {
        "name": "Prospect",
        "id": "1"
      },
      {
        "name": "PreQualification",
        "id": "2"
      },
  }
]

It seems like I'm going to need to extract them individually and load them into different tables in Postgres. Is there a way to write a schema for this that defines

_id

in a way that the target-postgres will be happy and treat the rest or the entire document as an object? I could then pull it apart with DBT in Postgres.

If you'd like to use a

jsonb

column for

value

instead of having the target attempt to denest it, the datamill-co variant is not a good choice: https://github.com/datamill-co/target-postgres/blob/8fb57db90992f7760c4eca4c1ee2631b0ab62c13/DECISIONS.md#de-nesting, and I suggest using the

transferwise

variant instead: https://github.com/transferwise/pipelinewise-target-postgres/blob/9470108c89b4a6f00420327ae2c66d48279d5cee/target_postgres/db_sync.py#L44-L45

Good point. I had forgotten that that tap doesn't explode out the relationship tables.

Try

type: array

👍 Lots of other odd results along the way. Once I got the jsonb column for value and an additional varchar called value_<timestamp>. Another time I only specified some of the fields and it figured everything else out including the jsonb columns. Keeps me guessing. Thanks for the help this morning.