I’m extracting documents from MongoDb into Postgres. I’m having problems accessing the nested objects in Postgres. I’m not sure if the fix is in the MongoDB source or my by increasing my skills in Postgres. Document in MongoDB.

{
    "_id": {
      "$oid": "60a413694f65a9002bdc4ff3"
    },
    "__v": 0,
    "activeStatus": {
      "_id": {
        "$oid": "60ad4c2262070d4a8410e537"
      },
      "statusId": 14,
      "taskId": 8,
      "createdOn": {
        "$date": "2021-05-25T19:12:34.045Z"
      },
  }

Selecting the data from the raw table.

select 
	b._airbyte_data::json
from 
	postgres.load._airbyte_raw_b b 
where
	b._airbyte_data::json ->> '_id' = '60a413694f65a9002bdc4ff3'

{
  "__v": 0,
  "_id": "60a413694f65a9002bdc4ff3",
  "activeStatus": "{\"statusId\"=>14, \"taskId\"=>8, \"createdOn\"=>2021-05-25 19:12:34.045 UTC}"
}

The problem I’m having is that the activeStatus column is a string not JSON and I don’t see how to easily parse it as the string is not formatted as JSON. I was expecting to find something more like this.

{
  "__v": 0,
  "_id": "60a413694f65a9002bdc4ff3",
  "activeStatus": "{\"statusId\":14, \"taskId\":8, \"createdOn\":\"2021-05-25 19:12:34.045 UTC\"}"
}

Were the activeStatus column was valid JSON and I could easily parse it with to_json() or that it would have been parsed with the ::json cast.

The only way I have found to get to this is through this parsing mess. Any better suggestions are welcome.

select
	replace(
		replace(
			regexp_replace( 
				regexp_replace(
					obj, 'On\"=>([0-9][0-9][0-9][0-9]-)', 'On"=>"\1', 'gm'
				), 'UTC([,}])', 'UTC"\1', 'gm'
			), '=>nil', '=>null'
		), '=>', ':'
	)::json -> 'statusId'
from 
	data

That's correct - it's an issue with nested objects returning as ruby hashes. The mongo connector is in ruby and is due to be converted any day now. Opened an issue awhile ago. Once that's complete, plain ol json_extract_path_text should get you there