JDBC Source Kafka Connect using multiple tables with resulting nested JSON and arrays

Kafka Connect
1

Kafka Connect and JDBC Source Connector.
I am trying to get a nested JSON with arrays from the tables:

/* Create tables, in this case DB2 */
CREATE TABLE contacts(
    contact_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(contact_id)
);

CREATE TABLE phones(
    phone_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    phone_no VARCHAR(20) NOT NULL,
    phone_type VARCHAR(10) NOT NULL,
    contact_id INT NOT NULL,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(phone_id),
    FOREIGN KEY (contact_id)
        REFERENCES contacts(contact_id)
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

/* Insert some data */
INSERT INTO contacts(first_name, last_name) 
    VALUES
        ('John','Doe');

INSERT INTO phones(phone_no, phone_type, contact_id)
    VALUES
	    ('Johns phone #1','HOME',1),
	    ('Johns phone #2','MOBILE',1),
	    ('Johns phone #3','WORK',1);

The JSON I would like get out on Kafka topic via Kafka Connect is something like this (minor adjustments are possible):

{
    "contact_id": 1,
    "first_name": "John",
    "last_name": "Doe",
    "modified_at": "2022-03-16T13:33:04.276",
    "phones":
    [
        {
            "phone_id": 1,
            "phone_no": "Johns phone #1",
            "phone_type": "HOME",
            "contact_id": 1,
            "modified_at": "2022-03-16T13:33:05.101"
        },
        {
            "phone_id": 2,
            "phone_no": "Johns phone #2",
            "phone_type": "MOBILE",
            "contact_id": 1,
            "modified_at": "2022-03-16T13:33:05.210"
        },
        {
            "phone_id": 3,
            "phone_no": "Johns phone #3",
            "phone_type": "WORK",
            "contact_id": 1,
            "modified_at": "2022-03-16T13:33:05.673"
        }
    ]
}

How can I do that with Kafka Connect (i.e. Kafka Connect config)?

2

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.