Why the JSON column cannot be a unique key?-CodePudding

I have a table, and inside it, a column called ColumnName and the data type of this column is JSON, and I'm trying to make it a unique key, but that is not possible, and I got this message:

#3152 - JSON column 'columnName' cannot be used in key specification.

I want to understand why I cannot make that column a unique key. Please leave the official documentation that talks about this.

Thank you.

Question updated at 28/11/2022:

I'll explain the actual usage of why I need to mark the JSON column as a unique key.

In my android app, I'm using OTP (One Time Password), and there are four usages for it:

- The first usage, when the user wants to log in to his account:

There is a field and a button. The user will enter his email inside the field and then press the button to continue.

In MySQL, I'll check whether the email exists inside the users' table. If the email exists, I'll send the code to his email (4 digits) and return {"REQUEST_STATUS": 100} to the user. Otherwise, I'll return {"REQUEST_STATUS": 740} to the user.

In my android app, if the REQUEST_STATUS was 100, I'll navigate the user to the verification page to continue the process, but if the REQUEST_STATUS was 740, I'll tell the user the email that entered does not exist.

- The second usage, when the user wants to create a new account:

There are two fields and a button. The user will enter his name inside the first field and his email inside the second field, and after that, will press the button to continue.

In MySQL, I'll check whether the email exists inside the users' table. If the email not exists, I'll send the code to his email (4 digits) and return {"REQUEST_STATUS": 100} to the user. Otherwise, I'll return {"REQUEST_STATUS": 813} to the user.

In my android app, if the REQUEST_STATUS was 100, I'll navigate the user to the verification page to continue the process, but if the REQUEST_STATUS was 813, I'll tell the user the email that entered already exists.

- The third usage, when the user wants to change his email:

There is a field and a button. The user will enter his new email inside the field and then press the button to continue.

In MySQL, I'll check whether the email exists inside the users' table. If the email not exists, I'll send the code to his new email (4 digits) and return {"REQUEST_STATUS": 100} to the user. Otherwise, I'll return {"REQUEST_STATUS": 813} to the user.

- The fourth usage, when the user wants to delete his account:

There is a button called "Delete my account", The user will press the button to continue.

I'll send the code to his email (4 digits) and return {"REQUEST_STATUS": 100} to the user.

In my android app, if the REQUEST_STATUS was 100, I'll navigate the user to the verification page to continue the process.

All the codes that are sent to emails I'll store it inside the codes table, and the table looks like this:

CREATE TABLE CODES (
    CODE CHAR(4) COLLATE UTF8_UNICODE_CI NOT NULL,
    EMAIL CHAR(200) COLLATE UTF8_UNICODE_CI NOT NULL,
    EXPIRATION_DATE DATETIME NOT NULL, -- Every code will be valid for 10 minutes only.
    NAME CHAR(25) COLLATE UTF8_UNICODE_CI NULL,
    NEW_EMAIL char(200) COLLATE UTF8_UNICODE_CI NULL,
    REMAINING_ATTEMPTS TINYINT(4) NOT NULL, -- Every code has 5 attempts only.
    TYPE TINYINT(4) NOT NULL
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COLLATE = UTF8_UNICODE_CI;

ALTER TABLE CODES ADD UNIQUE KEY (EMAIL,TYPE);

The type column will hold values from 1 to 4 only.

If the user wants to log in, then the value of the type column will be 1, 2 for creating a new account, 3 for changing the email, and 4 for deleting the account.

The name column will be null if the value of the type column is 1, 3, or 4, and The new_email column will be null if the value of the type column is 1, 2, or 4.

I wouldn't say I like designing the table this way, so I changed the table design to this:

CREATE TABLE CODES (
    CODE CHAR(4) COLLATE UTF8_UNICODE_CI NOT NULL,
    EMAIL CHAR(200) COLLATE UTF8_UNICODE_CI NOT NULL,
    EXPIRATION_DATE DATETIME NOT NULL, -- Every code will be valid for 10 minutes only.
    REMAINING_ATTEMPTS TINYINT(4) NOT NULL, -- Every code has 5 attempts only.
    TYPE JSON NOT NULL -- It will look like this {"TYPE": 1} OR {"TYPE": 2, "NAME": "..."} OR {"TYPE": 3, "NEW_EMAIL": "..."} OR {"TYPE": 4}
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COLLATE = UTF8_UNICODE_CI;

It looks better now, but I cannot make the email and the type columns a unique key.

According to `Bill Karwin's answer below, I read Indexing a Generated Column to Provide a JSON Column Index, and I understood how I could do it.

But the problem with this way is that I need to create an extra column to take the value from the field inside JSON.

Is it possible to make the email column and the type field inside JSON a unique key without creating an extra column?

CodePudding user response：

This is explained on a manual page that is quite obvious — the page for the JSON Data Type:

https://dev.mysql.com/doc/refman/8.0/en/json.html

JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column. See Indexing a Generated Column to Provide a JSON Column Index, for a detailed example.

You can create a unique index on a scalar value extracted from a JSON document, but you can't create an index on the whole JSON document.

The same is true of TEXT and BLOB. The reason is that these long data types are too large for an index. Each single entry in an index must fit on a database page, but JSON, TEXT, and BLOB may span many pages.

Re your update:

In MySQL 8.0, you can create an index on an expression without creating the intermediary virtual column. If you use MySQL 5.7, you need to upgrade to get this feature.

Demo:

mysql> ALTER TABLE CODES ADD UNIQUE KEY (EMAIL, (CAST(TYPE->>'$.TYPE' AS UNSIGNED)));

mysql> insert into CODES values ('blah', '[email protected]', now(), 0, '{"TYPE":1}');
Query OK, 1 row affected (0.01 sec)

mysql> insert into CODES values ('blah', '[email protected]', now(), 0, '{"TYPE":1}');
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'codes.functional_index'

mysql> insert into CODES values ('blah', '[email protected]', now(), 0, '{"TYPE":2}');
Query OK, 1 row affected (0.00 sec)