Qlik Replicate and MongoDB source: JSON Document Data Formats

[john_wang]

In this article, let's learn how to use the different JSON format in the MongoDB source endpoint.

JSON is a data format that represents the values of objects, arrays, numbers, strings, booleans, and nulls. The JSON format defines a reserved set of keys prefixed with "$" to represent field type information that directly corresponds to each type in BSON.

Sometimes we want to use a different format for downstream applications. This article explains the following topics:

• What MongoDB JSON formats are supported in Qlik Replicate?
• How do you define a JSON format in Qlik Replicate?
• An input sample in MongoDB and different output formats in the target database
• Environment

What MongoDB JSON formats are supported in Qlik Replicate? 

Qlik Replicate supports 3 different JSON formats:

• STRICT
• RELAXED
• EXTENDED

Qlik Replicate does not support Shell format at present.

How do you define a JSON format in Qlik Replicate?

The JSON format used by Qlik Replicate is defined as an Internal Parameter . For more information on how to set and use Internal Parameter, see Qlik Replicate: How to set Internal Parameters and what are they for? 

1. Open MongoDB source endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4. Add a new parameter named jsonMode
5.  <Enter> and select one from the available list:
   
   
   • STRICT
   • RELAXED
   • EXTENDED
     
     
     

An input sample in MongoDB and different output formats in the target database

In the MongoDB source collection, a new document is created as below (the date is in ISODate format):

{"id":22,"name":"Hi MongoDB","ReleaseDate":{"$date": "2023-09-16T09:19:29.999Z"}}

In the target side tables, we get different format data rows; the DATE fields values format can be ISODate format, or Epoch format, and if associated with additional data type properties upon different jsonMode setting.

jsonMode	data rows in target database
STRICT	\"id\": 22, \"name\": \"Hi MongoDB\", \"ReleaseDate\": {\"$date\": 1694855969999}
RELAXED	\"id\": 22, \"name\": \"Hi MongoDB\", \"ReleaseDate\": {\"$date\": \"2023-09-16T09:19:29.999Z\"}
EXTENDED	\"id\": {\"$numberInt\": \"22\"}, \"name\": \"Hi MongoDB\", \"ReleaseDate\": {\"$date\": {\"$numberLong\": \"1694855969999\"}}

 

Environment

Qlik Replicate (up to version 2023.5)
MongoDB source (all versions)

Related Content

Support case, #00106590