CODEWITHSUNDEEP
mysqljsonindexingunique
Yusup Bugacheev
Yusup Bugacheev

Reputation: 13

Make unique index on json column

How to make unique index on json type column in mysql? for example, I have translations column with slug property. I need unique slug among languages translations column image

CREATE TABLE `posts` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `root_id` int(10) unsigned NOT NULL, `translations` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `published_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `published_till` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `display_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `images` longtext COLLATE utf8mb4_unicode_ci, `deleted_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `posts_user_id_foreign` (`user_id`), KEY `posts_root_id_foreign` (`root_id`), CONSTRAINT `posts_root_id_foreign` FOREIGN KEY (`root_id`) REFERENCES `roots` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `admins` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Upvotes: 0

Views: 1629

Answers (1)

Rick James
Rick James

Reputation: 142298

JSON is not a replacement for the features of RDBMS.

As you insert rows, extract the "slug property" and put it into a separate column that is declared to be UNIQUE. Although it takes a little more work, it will be cleaner and more efficient in the long run.

Ditto for any other columns that you frequently need to filter or sort on. (Don't do it to all columns.)

Upvotes: 3

Related Questions