2

I have two tables in a one-to-many relation, created a while ago, not very well thought out.

CREATE TABLE DATA_CONNECTION ( ID bigint(20) NOT NULL, OWNER_ID bigint(20) NOT NULL, PROTOCOL_ID bigint(20) NOT NULL, CONNECTION_METHOD smallint(6) NOT NULL, ADDRESS varchar(255), PORT INTEGER, USERNAME varchar(255), PASSWORD varchar(255), FILE_ENCRYPTION_PASSWORD varchar(255), SECONDARY_PASSWORD varchar(255), HOST_KEY varchar(255), ENCRYPTION_TYPE varchar(255), PUBLIC_KEY_FILE_NAME varchar(255), PRIVATE_KEY_FILE_NAME varchar(255), DATA_FILE_NAME_REGEX varchar(255), METADATA_FILE_NAME_REGEX varchar(255), REMOTE_FILE_NAME_REGEX varchar(255), TIME_ZONE varchar(255), IS_ENABLED tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (ID), CONSTRAINT FK_OWNER_ID FOREIGN KEY (OWNER_ID) REFERENCES USER (ID) ON DELETE CASCADE, CONSTRAINT FK_PROTOCOL_ID FOREIGN KEY (PROTOCOL_ID) REFERENCES PROTOCOL (ID) ON DELETE RESTRICT ); CREATE TABLE DATA_CONNECTION_FRAGMENT ( ID bigint(20) NOT NULL, DATA_CONNECTION_ID bigint(20) NOT NULL, NAME varchar(255), AUTH varchar(255), IS_ENABLED tinyint(1), PRIMARY KEY (ID), CONSTRAINT FK_DATA_CONNECTION_ID FOREIGN KEY (DATA_CONNECTION_ID) REFERENCES DATA_CONNECTION (ID) ON DELETE CASCADE );

The problem is that a many of the fields in DATA_CONNECTION are optional. Depending on the PROTOCOL_ID and the CONNECTION_METHOD, some are used and the others are null. I would like to just have a CONNECTION_DETAILS column as a json type, including the DATA_CONNECTION_FRAGMENT table. Like this.

CREATE TABLE DATA_INTEGRATION ( ID bigint(20) NOT NULL, OWNER_ID bigint(20) NOT NULL, PROTOCOL_ID bigint(20) NOT NULL, CONNECTION_METHOD smallint(6) NOT NULL, IS_ENABLED tinyint(1) NOT NULL DEFAULT 0, CONNECTION_DETAILS JSON, -- Something like this {address: string, password: string, fragments: [{name: string, auth: string}, ...etc], ...etc} CONSTRAINT FK_OWNER_ID FOREIGN KEY (OWNER_ID) REFERENCES USER (ID) ON DELETE CASCADE, CONSTRAINT FK_PROTOCOL_ID FOREIGN KEY (PROTOCOL_ID) REFERENCES PROTOCOL (ID) ON DELETE RESTRICT );

So, is there any way to migrate the data with only a sql command? Something like this maybe?

INSERT INTO DATA_INTEGRATION ( ID, PROTOCOL_ID, CONNECTION_METHOD, OWNER_ID, IS_ENABLED, CONNECTION_DETAILS ) SELECT C.ID, C.PROTOCOL_ID, C.CONNECTION_METHOD, C.OWNER_ID, C.IS_ENABLED, JSON_OBJECT ( 'address', C.ADDRESS, 'port', C.PORT, 'username', C.USERNAME, 'password', C.PASSWORD, 'secondaryPassword', C.SECONDARY_PASSWORD, 'hostKey', C.HOST_KEY, 'encryptionType', C.ENCRYPTION_TYPE, 'publicKeyFileName', C.PUBLIC_KEY_FILE_NAME, 'privateKeyFileName', C.PRIVATE_KEY_FILE_NAME, 'dataFilenameRegex', C.DATA_FILENAME_REGEX, 'metadataFilenameRegex', C.METADATA_FILENAME_REGEX, 'remoteFilenameRegex', C.REMOTE_FILENAME_REGEX, 'timeZone', C.TIME_ZONE, 'fragments', JSON_ARRAYAGG(JSON_OBJECT( 'name', F.NAME, 'auth', F.AUTH, 'isEnabled', F.IS_ENABLED )) ) FROM DATA_CONNECTION C JOIN DATA_CONNECTION_FRAGMENT F ON F.DATA_CONNECTION_ID = C.ID;

The above isn't working for me, but I'm not sure what I can do to fix it. It only returns one row. Maybe the JSON_ARRAYAGG is breaking it? Maybe I'm not using the right kind of JOIN?

The existing tables have quite a small number of records, so I'm not worried about stressing the db with this.

Thanks!

Improve this question
1
  • Using a LEFT JOIN and JSON_ARRAY instead of JSON_ARRAYAGG results in one record per FRAGMENT, btw. Commented Sep 9, 2022 at 17:33

2 Answers 2

Reset to default
1

The JSON_ARRAYAGG() is an aggregating function, like COUNT() or SUM(). If you use this in your query without specifying a GROUP BY, the default is to reduce the result to a single "group" (i.e. a single row).

I think in this case you want to GROUP BY C.ID so the result has one row per collection, and then the JSON_ARRAYAGG() will aggregate into an array only the fragments for the respective collection.

SELECT C.ID, ... JSON_OBJECT ( ... 'fragments', JSON_ARRAYAGG(JSON_OBJECT( 'name', F.NAME, 'auth', F.AUTH, 'isEnabled', F.IS_ENABLED )) ) FROM DATA_CONNECTION C JOIN DATA_CONNECTION_FRAGMENT F ON F.DATA_CONNECTION_ID = C.ID GROUP BY C.ID;

I have to comment that I wouldn't recommend to convert a perfectly normal table into JSON. There is no problem leaving several columns NULL in a table.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you! Yes, I would normally agree with your final statement about not doing this. What I failed to mention in the question though is that it's foreseen that we'll be adding many many new protocols and connection methods, which will mean an explosion of new columns in the table, each slightly different and used only by its corresponding protocol. If this weren't the case, I wouldn't be worrying about it so much.
Accepting this answer, as it fixes the problem presented in the oq in the simplest way. However, @Dennis Williams' answer also works on older versions of MySQL without JSON_ARRAYAGG, so that's awesome.
All currently supported versions of MySQL (i.e. not past their end-of-life) support JSON_ARRAYAGG().
1

You can get the results you need by using a sub-select instead of a join:

 SELECT C.ID, C.PROTOCOL_ID, C.CONNECTION_METHOD, C.OWNER_ID, C.IS_ENABLED, JSON_OBJECT ( 'address', C.ADDRESS, 'port', C.PORT, 'username', C.USERNAME, 'password', C.PASSWORD, 'secondaryPassword', C.SECONDARY_PASSWORD, 'hostKey', C.HOST_KEY, 'encryptionType', C.ENCRYPTION_TYPE, 'publicKeyFileName', C.PUBLIC_KEY_FILE_NAME, 'privateKeyFileName', C.PRIVATE_KEY_FILE_NAME, 'dataFilenameRegex', C.DATA_FILENAME_REGEX, 'metadataFilenameRegex', C.METADATA_FILENAME_REGEX, 'remoteFilenameRegex', C.REMOTE_FILENAME_REGEX, 'timeZone', C.TIME_ZONE, 'fragments', ( SELECT CAST( CONCAT('[', GROUP_CONCAT( JSON_OBJECT( 'name', F.NAME, 'auth', F.AUTH, 'isEnabled', F.IS_ENABLED ) ), ']' ) AS JSON) FROM DATA_COLLECTION_FRAGMENT F WHERE F.DATA_COLLECTION_ID = C.ID ) ) FROM DATA_COLLECTION C;

See How do I generate nested json objects using mysql native json functions?

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.