User Guide

1.3
- 1.2
- 1.1
- 1.0

Back Next

Custom JSON Examples

These custom patterns show examples of a simple JSON view of a table for insert, update, and delete CDC events, patterns that format JSON for each event type, and a JSON example that uses multiple tag types.

After you create a JSON pattern, you can validate the JSON pattern using a JSON validator such as jsonlint.com. Copy only the pattern into the validator and then test it. Do not copy the section tags. Replace the keywords with test data. Repeat the test for each pattern you want to validate.

Simple JSON Example

Company XYZ wants a simple JSON view of insert, update, and delete events on the My.Table table. The message format should contain only column names as keys with their associated values for each of the event types. The table columns are named AA_field, AB_field, and AC_field.

The <REPEAT_DELIMITER> tag places the delimiter comma between the repeating group of items in the message. The last repeated group has the delimiter removed.

The custom pattern file for My.Table contains the following specifications:


# Special attribute that will place a comma between all of the repeating group items
<SPECIAL_ATTRIBUTES>
   <REPEAT_DELIMITER>,</REPEAT_DELIMITER>
</SPECIAL_ATTRIBUTES>

# Exclude Informatica generated columns
<COLUMN_ATTRIBUTES>
  <EXCLUDE> <column_name>    INFA*         				</column_name> </EXCLUDE>
  <EXCLUDE> <column_name>    DTL*          				</column_name> </EXCLUDE>
  <EXCLUDE> <column_name>    *_Present   			</column_name> </EXCLUDE>
  <EXCLUDE> <column_name>    *_BeforeImage   		</column_name> </EXCLUDE>
  <EXCLUDE> <column_name>    *_BeforeImage_Present   </column_name> </EXCLUDE>
</COLUMN_ATTRIBUTES>

# For insert events 
<INSERT_EVENT>
{“evnt”:“<event_type>”,<REPEAT>“<column_name>”: “<column_value>”</REPEAT>}
</INSERT_EVENT>

#For update events
<UPDATE_EVENT>
{“evnt”:“<event_type>”,<REPEAT>“<column_name>”: “<column_value>”</REPEAT>}
</UPDATE_EVENT>

#For delete events
<DELETE_EVENT>
{“evnt”:”<event_type>”,<REPEAT>“<column_name>”: “<column_value>”</REPEAT>}
</DELETE_EVENT>

Insert Event Example

The following example shows a pattern that formats JSON for an insert event:


<INSERT_EVENT>
        {
		"Event" : "<event_type>”  ,
		“table_name” : "<table_name>" , 
		"transaction" :"<transaction_id>" ,
		“sequence” : “<sequence>” ,
		}, 
		“column_Information” ; [
		< REPEAT >
			 {
				“column_name" : "<column_name>" ,
				“column_type" : “<column_type>" ,
				“column_value" : <column_value>” 
			}
		</REPEAT>
		] }
</INSERT_EVENT>

Update Event Example

The following example shows a pattern that formats JSON for an update event:


<UPDATE_EVENT>
		{
		"Event" : "<event_type>” ,
		“table_name” : "<table_name>" , 
		"transaction" :"<transaction_id>" ,
		“sequence” : “<sequence>” ,
		“column_information” : [
		<REPEAT>
				{
				“column_name" : “<column_name>" ,
				“column_type" : “<column_type>” ,
				“column_value” : "<column_value>" ,
				“before_Image_value" : “<column_beforeImage_value>” ,
				“column_before_Image_Present" : “<column_beforeImage_isPresent>”
				}
		</REPEAT>
		] }
</UPDATE_EVENT>

Delete Event Example

The following example shows a pattern that formats JSON for a delete event:


<DELETE_EVENT>
		{
		"Event" : "<event_type>” ,
		“table_name” : "<table_name>" , 
		"transaction" :"<transaction_id>" ,
		“sequence” : “<sequence>” ,
		“column_information” : [
		< REPEAT >
				{
			“column_name" : "<column_name>" ,
   		 “column_type" : “<column_type>" ,
   		 “column_value" : <column_value>” ,
				}
		</REPEAT>
		] }
"</DELETE_EVENT>",

Commit Event Example

The following example shows a pattern that formats JSON for a commit event:


<COMMIT_EVENT>
		 {
		 "Event" : "<event_type>”
		 "transaction" :"<transaction_id>"
		 }
</COMMIT_EVENT>

JSON Example that Uses Multiple Tag Types

This example produces a JSON format for insert and delete events for an Oracle object. The result will be a condensed JSON string that contains the following information:

The event types are of insert and delete only.

The num1 and num2 columns of the object have a number data type.

A column that contains the creation date for the change record in the format yyyyMMdd.

The table is defined by the following Oracle DDL:


CREATE TABLE "XXX"."NUMTBL3" 
 ("num1" NUMBER(2) NOT NULL ENABLE, 
  "num2" NUMBER(10), 
   PRIMARY KEY ("num1") …

In the PowerExchange CDC capture registrations, the table name is numtbl3_NUMTBL3.

In the custom pattern, which is defined for numtbl3_NUMTBL3, the following tags define column attribute processing:

Use the <EXCLUDE> tag to exclude all columns except for DTL__CAPXTIMESTAMP, which is the source creation timestamp for the row.

Use the <DT_SWAP> tag to define the following processing:

Convert the DTL__CAPXTIMESTAMP column, which is a “long” data type from PowerExchange CDC to a date format.

Swap the num2 column from a string data type to an int32 data type.

Use the <DT_MASK> tag to convert DTL__CAPXTIMESTAMP into the format yyyyMMdd.

Use the <DT_QUOTES> tag to define the following processing:

Explicitly state that any value for data type int32 will not have its value wrapped in double-quotation marks.

The data type string will have its value quoted with a double-quotation mark.

The data type date will have its value quoted with a double-quotation mark.

The data type binary will have its value quoted with a double-quotation mark. Even though there are no binary items produced, it is safe to include them because they are ignored in this case.

The <data_value> tag is not quoted in the syntax of the insert and delete event.


<SPECIAL_ATTRIBUTES>
<DELIMITER>NEWLINE</DELIMITER>
<REPEAT_DELIMITER>, NEWLINE</REPEAT_DELIMITER>
</SPECIAL_ATTRIBUTES>
 
<COLUMN_ATTRIBUTES>
# exclude columns
  <EXCLUDE><column_name>*_Present</column_name></EXCLUDE>
  <EXCLUDE><column_name>*_BeforeImage</column_name></EXCLUDE>
  <EXCLUDE><column_name>*_BeforeImage_Present</column_name></EXCLUDE>
  <EXCLUDE><column_name>DTL__CAPXACTION</column_name></EXCLUDE>
  <EXCLUDE><column_name>DTL__CAPXRESTART1</column_name></EXCLUDE>
  <EXCLUDE><column_name>DTL__CAPXRESTART2</column_name></EXCLUDE>
  <EXCLUDE><column_name>DTL__CAPXROWID</column_name></EXCLUDE>
  <EXCLUDE><column_name>DTL__CAPXUOW</column_name></EXCLUDE>
  <EXCLUDE><column_name>DTL__CAPXUSER</column_name></EXCLUDE>
  <EXCLUDE><column_name>INFA_SEQUENCE</column_name></EXCLUDE>
  <EXCLUDE><column_name>INFA_OP_TYPE</column_name></EXCLUDE>
  <EXCLUDE><column_name>INFA_TABLE_NAME</column_name></EXCLUDE>
# swap data types
  <DT_SWAP><column_name>DTL__CAPXTIMESTAMP</column_name><old_data_type>long</old_data_type><new_data_type>date</new_data_type></DT_SWAP>
  <DT_SWAP><column_name>num2</column_name><old_data_type>string</old_data_type><new_data_type>int32</new_data_type></DT_SWAP>
  <DT_SWAP><old_data_type>integer</old_data_type><new_data_type>int32</new_data_type></DT_SWAP>
# masks
  <DT_MASK><column_name>DTL__CAPXTIMESTAMP</column_name><format>yyyyMMdd</format></DT_MASK>
# dont use quotes on these data types 
  <DT_QUOTES><data_type>int32</data_type><quote_character></quote_character></DT_QUOTES>  
# use quotes on these data types 
  <DT_QUOTES><data_type>string</data_type><quote_character>"</quote_character></DT_QUOTES>  
  <DT_QUOTES><data_type>date</data_type><quote_character>"</quote_character></DT_QUOTES>  
  <DT_QUOTES><data_type>binary</data_type><quote_character>"</quote_character></DT_QUOTES>  
</COLUMN_ATTRIBUTES>
 
 
<INSERT_EVENT>
{ "event" : "insert", "columns" : [ <delimiter>
<REPEAT> { "column" : "<column_name>", "<column_type>" : <column_value> } </REPEAT>
]
}
</INSERT_EVENT>
 
<DELETE_EVENT>
{
"event" : "delete", "columns" : [ <delimiter>
<REPEAT>  { "column" : "<column_name>", "<column_type>" : <column_value > } </REPEAT> 
]
}
</DELETE_EVENT>

The following example shows the output for an insert event that has columns num1 = 27 and column num2 = 270:


{ "event" : "insert", "columns" : [
{ "column" : "DTL__CAPXTIMESTAMP", "date" : "20200812" } ,
{ "column" : "num1", "int32" : 27 } ,
{ "column" : "num2", "int32" : 270 } ]}

Appendix C: Custom Pattern Formats

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal

0 COMMENTS

Back Next

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

User Guide

User Guide

Custom JSON Examples

Custom JSON Examples

Simple JSON Example

Insert Event Example

Update Event Example

Delete Event Example

Commit Event Example

JSON Example that Uses Multiple Tag Types