Replicate - How to filter records by unique identifier "_id" in MongoDB source

[john_wang]

How to filter records by unique identifier "_id" in MongoDB source?

In MongoDB each document has a unique identifier field named "_id", it's generated automatically (by default). Replicate will replicate this field to target side. The field can be used to filter records.

By default the "_id" field was composed by 12 bytes. Below sample shows how to use the first 4 bytes to filter records. 

position	detailed explanation
	626509d4582db56b600232fb
4 bytes	Representing the seconds since the Unix epoch
5 bytes	random value
3 bytes	counter, starting with a random value

The 4 bytes are Hexadecimal value of Unix epoch seconds, it can be converted to meaningful time by the page:

Hex	Dec	https://www.epochconverter.com/
626509d4	1650788820	Sunday, April 24, 2022 4:27:00 PM GMT+08:00

Environment

• All Supported Qlik Replicate #Version
• All Supported MongoDB versions (4.x)

Detailed information

1. Uses the "_id" in task filter, for example

Qlik Replicate 

[jyeragi]

John,

Instead of epoch time in the filter, can we pass a dynamic string ?

e.g: 

In Mongo to get last 10 days data,

db.appraisalBM.count({_id:{$gte:ObjectId.fromDate(new Date(ISODate().setHours(00,00,00,00)-10*24*60*60000)), $lte:ObjectId.fromDate(new Date(ISODate().setHours(23,59,59,999)-2*24*60*60000))}})

in QLIKReplicate can we do similar?

[john_wang]

Hello @jyeragi ,

Excuse me for the delay, I just missed your update.

IN short I have not a better way at present.

In SQLite it's easy to get the current date Julianday value eg

strftime('%s', 'now')

the output is decimal values like 1652842269 (means May 17 2022) however it's hard to transfer it to Hexadecimal value in SQLite to compare with _id value eg 626509d4 (Apr 24 2022).

I will research it further. If I get any progress I will let you know.

Regards,

John.