Edit

Share via

JSON message format - change event streaming

SQL Server 2025 (17.x) Preview

This article describes the JSON format of a CloudEvents message that is streamed from SQL Server to Azure Event Hubs when using the change event streaming (CES) feature introduced in SQL Server 2025 (17.x) Preview.

Note

Change event streaming is currently in preview for SQL Server 2025.

Overview

Events emitted by change event streaming follow the CloudEvents specification, making them easy to integrate with event-driven systems. All CES CloudEvents contain 11 attributes (fields). CES can be configured to serialize CloudEvents as JSON (native), or as Avro binary. The following sections of this article describe the message format in detail, including CES CloudEvent attributes, and serialization.

When applicable, the descriptions in this section are taken from CloudEvent specification, which includes additional details.

Attributes

  • specversion:

    • Data type: String
    • Required CloudEvent attribute
    • The version of the CloudEvents specification which the event uses. This enables the interpretation of the context.

  • type

    • Data type: String
    • Required CloudEvent attribute
    • Contains a value that describes the type of event related to the originating occurrence. Often this attribute is used for routing, observability, or policy enforcement. The format of this is defined by the producer and might include information such as the version of the type. For more information, review Versioning of CloudEvents.

  • source

    • Data type: String
    • Required CloudEvent attribute
    • Identifies the context in which an event happened. Source + ID must be unique for each event.

  • id

    • Data type: String
    • Required CloudEvent attribute
    • Identifies the event. Producers must ensure that source + ID is unique for each distinct event. If a duplicate event is resent (for example, due to a network error) it could have the same ID. Consumers might assume that events with identical source and ID are duplicates.

  • logicalid

    • Data type: String
    • Extension attribute
    • Split messages (due to Event Hubs msg size restrictions) are identified by shared logical IDs.

  • time

    • Data type: Timestamp
    • Optional CloudEvent attribute
    • Timestamp of when the occurrence happened. If the time of the occurrence can't be determined then this attribute might be set to some other time (such as the current time) by the CloudEvents producer. All producers for the same source must be consistent in this respect - either they all use the actual time of the occurrence, or they all use the same algorithm to determine the value used.

  • datacontenttype

    • Data type: String
    • Optional CloudEvent attribute
    • Content type of data value. This attribute enables data to carry any type of content, whereby format and encoding might differ from that of the chosen event format. For example, an event rendered using the JSON envelope format might carry an XML payload in the data, and the consumer is informed by this attribute being set to "application/xml". The rules for how data content is rendered for different datacontenttype values are defined in the event format specifications; for example, the JSON event format defines the relationship in section 3.1.

  • operation

    • Data type: String
    • Extension
    • Represents the type of SQL operation that happened.

  • segmentindex

    • Data type: Integer
    • Extension attribute
    • Segment index, that denotes the position of the message within the logical message chunks. The segment index provides information about where the message stands in the sequence of logical message fragments. In this implementation of change event streaming, this field is always present.

  • finalsegment

    • Data type: Boolean
    • Extension attribute
    • Tells if this segment is final segment of the sequence. In this implementation of change event streaming, this field is always present.

  • data

    • Data type: String
    • Optional CloudEvent attribute
    • Domain specific event data. For CES, data is string that can be parsed as JSON. This JSON describes how data changed. Format of the data attribute is at Data attribute format.

Examples

JSON message example - insert

{
  "specversion": "1.0",
  "type": "com.microsoft.SQL.CES.DML.V1",
  "source": "\/",
  "id": "d43f09a6-d13b-4902-86d4-17bdb5edb872",
  "logicalid": "9c8d4ad2-bf54-4f10-a96f-038af496997f:0000002C00000300017C:00000000000000000001",
  "time": "2025-03-14T16:45:20.650Z",
  "datacontenttype": "application\/json",
  "operation": "INS",
  "splitindex": 0,
  "splittotalcnt": 0,
  "data": "{\n  \"eventsource\": {\n    \"db\": \"db1\",\n    \"schema\": \"dbo\",\n    \"tbl\": \"Purchases\",\n    \"cols\": [\n      {\n        \"name\": \"purchase_id\",\n        \"type\": \"int\",\n        \"index\": 0\n      },\n      {\n        \"name\": \"customer_name\",\n        \"type\": \"varchar(100)\",\n        \"index\": 1\n      },\n      {\n        \"name\": \"product_id\",\n        \"type\": \"int\",\n        \"index\": 2\n      },\n      {\n        \"name\": \"product_name\",\n        \"type\": \"varchar(100)\",\n        \"index\": 3\n      },\n      {\n        \"name\": \"price_per_item\",\n        \"type\": \"int\",\n        \"index\": 4\n      },\n      {\n        \"name\": \"quantity\",\n        \"type\": \"int\",\n        \"index\": 5\n      },\n      {\n        \"name\": \"purchase_date\",\n        \"type\": \"datetime\",\n        \"index\": 6\n      },\n      {\n        \"name\": \"payment_method\",\n        \"type\": \"varchar(50)\",\n        \"index\": 7\n      }\n    ],\n    \"pkkey\": [\n      {\n        \"columnname\": \"purchase_id\",\n        \"value\": \"105\"\n      }\n    ]\n  },\n  \"eventrow\": {\n    \"old\": \"{}\",\n    \"current\": \"{\\\"purchase_id\\\": \\\"105\\\", \\\"customer_name\\\": \\\"Anna Doe\\\", \\\"product_id\\\": \\\"101\\\", \\\"product_name\\\": \\\"Game 2077\\\", \\\"price_per_item\\\": \\\"60\\\", \\\"quantity\\\": \\\"1\\\", \\\"purchase_date\\\": \\\"2025-03-14 16:45:01.000\\\", \\\"payment_method\\\": \\\"Credit Card\\\"}\"\n  }\n}"
}

JSON message example – updated

{
  "specversion": "1.0",
  "type": "com.microsoft.SQL.CES.DML.V1",
  "source": "\/",
  "id": "c425575f-00bb-45cf-acec-c55fdc7d08cd",
  "logicalid": "9c8d4ad2-bf54-4f10-a96f-038af496997f:0000002C000003500004:00000000000000000001",
  "time": "2025-03-14T16:49:59.567Z",
  "datacontenttype": "application\/json",
  "operation": "UPD",
  "splitindex": 0,
  "splittotalcnt": 0,
  "data": "{\n  \"eventsource\": {\n    \"db\": \"db1\",\n    \"schema\": \"dbo\",\n    \"tbl\": \"Purchases\",\n    \"cols\": [\n      {\n        \"name\": \"purchase_id\",\n        \"type\": \"int\",\n        \"index\": 0\n      },\n      {\n        \"name\": \"customer_name\",\n        \"type\": \"varchar(100)\",\n        \"index\": 1\n      },\n      {\n        \"name\": \"product_id\",\n        \"type\": \"int\",\n        \"index\": 2\n      },\n      {\n        \"name\": \"product_name\",\n        \"type\": \"varchar(100)\",\n        \"index\": 3\n      },\n      {\n        \"name\": \"price_per_item\",\n        \"type\": \"int\",\n        \"index\": 4\n      },\n      {\n        \"name\": \"quantity\",\n        \"type\": \"int\",\n        \"index\": 5\n      },\n      {\n        \"name\": \"purchase_date\",\n        \"type\": \"datetime\",\n        \"index\": 6\n      },\n      {\n        \"name\": \"payment_method\",\n        \"type\": \"varchar(50)\",\n        \"index\": 7\n      }\n    ],\n    \"pkkey\": [\n      {\n        \"columnname\": \"purchase_id\",\n        \"value\": \"105\"\n      }\n    ]\n  },\n  \"eventrow\": {\n    \"old\": \"{}\",\n    \"current\": \"{\\\"purchase_id\\\": \\\"105\\\", \\\"customer_name\\\": \\\"Anna Doe\\\", \\\"product_id\\\": \\\"100\\\", \\\"product_name\\\": \\\"Game 2066\\\", \\\"price_per_item\\\": \\\"50\\\", \\\"quantity\\\": \\\"2\\\", \\\"purchase_date\\\": \\\"2025-03-14 16:45:01.000\\\", \\\"payment_method\\\": \\\"Credit Card\\\"}\"\n  }\n}"
}

JSON message example - delete

{
  "specversion": "1.0",
  "type": "com.microsoft.SQL.CES.DML.V1",
  "source": "\/",
  "id": "24fa0c2c-c45d-4abf-9a8d-fba04c29fc86",
  "logicalid": "9c8d4ad2-bf54-4f10-a96f-038af496997f:0000002C000003600019:00000000000000000001",
  "time": "2025-03-14T16:51:39.613Z",
  "datacontenttype": "application\/json",
  "operation": "DEL",
  "splitindex": 0,
  "splittotalcnt": 0,
  "data": "{\n  \"eventsource\": {\n    \"db\": \"db1\",\n    \"schema\": \"dbo\",\n    \"tbl\": \"Purchases\",\n    \"cols\": [\n      {\n        \"name\": \"purchase_id\",\n        \"type\": \"int\",\n        \"index\": 0\n      },\n      {\n        \"name\": \"customer_name\",\n        \"type\": \"varchar(100)\",\n        \"index\": 1\n      },\n      {\n        \"name\": \"product_id\",\n        \"type\": \"int\",\n        \"index\": 2\n      },\n      {\n        \"name\": \"product_name\",\n        \"type\": \"varchar(100)\",\n        \"index\": 3\n      },\n      {\n        \"name\": \"price_per_item\",\n        \"type\": \"int\",\n        \"index\": 4\n      },\n      {\n        \"name\": \"quantity\",\n        \"type\": \"int\",\n        \"index\": 5\n      },\n      {\n        \"name\": \"purchase_date\",\n        \"type\": \"datetime\",\n        \"index\": 6\n      },\n      {\n        \"name\": \"payment_method\",\n        \"type\": \"varchar(50)\",\n        \"index\": 7\n      }\n    ],\n    \"pkkey\": [\n      {\n        \"columnname\": \"purchase_id\",\n        \"value\": \"105\"\n      }\n    ]\n  },\n  \"eventrow\": {\n    \"old\": \"{\\\"purchase_id\\\": \\\"105\\\", \\\"customer_name\\\": \\\"Anna Doe\\\", \\\"product_id\\\": \\\"100\\\", \\\"product_name\\\": \\\"Game 2066\\\", \\\"price_per_item\\\": \\\"50\\\", \\\"quantity\\\": \\\"2\\\", \\\"purchase_date\\\": \\\"2025-03-14 16:45:01.000\\\", \\\"payment_method\\\": \\\"Credit Card\\\"}\",\n    \"current\": \"{}\"\n  }\n}"
}

Data attribute format

Data is a JSON object wrapped in string attribute that contains two attributes:

  • eventSource
  • eventRow
"data": "{ "eventsource": {<eventSource>}, "eventdata": {<eventData>}}"

Details of these two attributes are explained in greater detail in the following sections:

eventsource

Describes the metadata about the database and the table where the event occurred:

  • db

    • Data type: String
    • Description: The name of the database where the table is located.
    • Example: cessqldb001

  • schema

    • Data type: String
    • Description: The database schema that contains the table.
    • Example: dbo

  • tbl

    • Data type: String
    • Description: The table in which the event occurred.
    • Example: Purchases

  • cols

    • Data type: Array
    • Description: An array detailing the columns in the table.
      • name (string): The name of the column.
      • type (string): The data type of the column (VARCHAR or INT).
      • index (integer): The index or position of the column in the table.

  • pkkey

    • Data type: Array
    • Description: Represents the primary key columns and their values for identifying the specific row.
      • columnname (string): The name of column used in the primary key.
      • value (string/int/etc.): The value for the column used in the primary key, helps to uniquely identify the row.

eventrow

Describes row-level changes and compares the old and current values of the fields in the record.

  • old (object wrapped in string): Represents the values in the row before the event.
    • Each key-value pair consists of:
      • <column_name>: (string): The name of the column.
      • <column_value>: (string/int/etc.): The previous value for that column.
  • current (object wrapped in string): Represents the updated values in the row after the event.
    • Similar to the old object, with each key-value pair structured as:
      • <column_name> (string): The name of the column.
      • <column_value> (string/int/etc.): The new or current value for that column.

CES CloudEvent JSON schema

{
  "type": "record",
  "name": "ChangeEvent",
  "fields": [
    {
      "name": "specversion",
      "type": "string"
    },
    {
      "name": "type",
      "type": "string"
    },
    {
      "name": "source",
      "type": "string"
    },
    {
      "name": "id",
      "type": "string"
    },
    {
      "name": "logicalid",
      "type": "string"
    },
    {
      "name": "time",
      "type": "string"
    },
    {
      "name": "datacontenttype",
      "type": "string"
    },
    {
      "name": "operation",
      "type": "string"
    },
    {
      "name": "segmentindex",
      "type": "int"
    },
    {
      "name": "finalsegment",
      "type": "boolean"
    },
    {
      "name": "data",
      "type": "bytes"
    }
  ]
}

CES data attribute JSON schema

{
  "name": "Data",
  "type": "record",
  "fields": [
    {
      "name": "eventsource",
      "type": {
        "name": "EventSource",
        "type": "record",
        "fields": [
          {
            "name": "db",
            "type": "string"
          },
          {
            "name": "schema",
            "type": "string"
          },
          {
            "name": "tbl",
            "type": "string"
          },
          {
            "name": "cols",
            "type": {
              "type": "array",
              "items": {
                "name": "Column",
                "type": "record",
                "fields": [
                  {
                    "name": "name",
                    "type": "string"
                  },
                  {
                    "name": "type",
                    "type": "string"
                  },
                  {
                    "name": "index",
                    "type": "int"
                  }
                ]
              }
            }
          },
          {
            "name": "pkkey",
            "type": {
              "type": "array",
              "items": {
                "name": "PkKey",
                "type": "record",
                "fields": [
                  {
                    "name": "columnname",
                    "type": "string"
                  },
                  {
                    "name": "value",
                    "type": "string"
                  }
                ]
              }
            }
          },
          {
            "name": "transaction",
            "type": {
              "name": "Transaction",
              "type": "record",
              "fields": [
                {
                  "name": "commitlsn",
                  "type": "string"
                },
                {
                  "name": "beginlsn",
                  "type": "string"
                },
                {
                  "name": "sequencenumber",
                  "type": "int"
                },
                {
                  "name": "committime",
                  "type": "string"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "name": "eventrow",
      "type": {
        "name": "EventRow",
        "type": "record",
        "fields": [
          {
            "name": "old",
            "type": "string"
          },
          {
            "name": "current",
            "type": "string"
          }
        ]
      }
    }
  ]
}

Related content