How to create a virtual column to index JSON column in MySQL?

Question

I am using MySql 8.0 verion and my table looks as below:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `tableType` varchar(45) DEFAULT NULL,
  `jkey` varchar(45) DEFAULT NULL,
  `jval` json DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Here jval will always have 2 fields as below:

{"group": "group1@abc.com", "user": "user1"}

{"group": "group2@abc.com", "user": "user2"}

I am trying to add index to the JSON column jval but getting below error:

Operation failed: There was an error while applying the SQL script to the database.
ERROR 3152: JSON column 'jval' supports indexing only via generated columns on a specified JSON path.
SQL Statement:
ALTER TABLE `db1`.`table1` 
ADD INDEX `jval` (`value` ASC) VISIBLE

How can I create a virtual column to index jval in MySQL?

MySQL doesn't have a way to index JSON documents directly, but it has given us an alternative: generated columns. we can index a JSON field. Can you share the sample json and the field to be indexed — Indra Kumar S, Jun 24 '21 at 15:11
Here `jval` will have 2 values as below: {group1@abc.com, sme1} {group2@abc.com, sme2} — meallhour, Jun 24 '21 at 15:15
Its not a valid json. Just share a proper json and the `field` to be indexed — Indra Kumar S, Jun 24 '21 at 15:16
Here is the valid one, ["group1@abc.com", "user1" ] ["group2@abc.com", "user2" ] — meallhour, Jun 24 '21 at 15:19
Please understand one thing if your json is going to be as simple as ["group1@abc.com", "user1" ] then why do you want it as JSON column ? We need a key to index the values — Indra Kumar S, Jun 24 '21 at 15:21
thanks a lot for providing right guidance. the updated json now looks as below: `{"group": "group1@abc.com", "user": "user1"} {"group": "group2@abc.com", "user": "user2"}` — meallhour, Jun 24 '21 at 15:26
I don't see a column named `value` -- so the index will fail. — Rick James, Jun 24 '21 at 19:55

Indra Kumar S · Accepted Answer · 2021-06-24T16:16:11.490

MySQL doesn't have a way to index JSON documents directly, but it has given us an alternative: generated columns. By generating columns from values within a JSON document and then indexing that column, we can practically index a JSON field.

Syntax for Generated Columns is

column_name datatype GENERATED ALWAYS AS (expression)

You query will become like

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `tableType` varchar(45) DEFAULT NULL,
  `jkey` varchar(45) DEFAULT NULL,
  `jval` json NOT NULL,

  `group_virtual` VARCHAR(250) GENERATED ALWAYS AS (`jval` ->> '$.group') NOT NULL, 
  `user_virtual` VARCHAR(250) GENERATED ALWAYS AS (`jval` ->> '$.user') NOT NULL,   
  
  PRIMARY KEY (`id`)
);

You can check the virtual columns

SHOW COLUMNS FROM `table1`;

Field	Type	Null	Key	Extra
id	696e74	NO	PRI
tableType	7661726368617228343529	YES
jkey	7661726368617228343529	YES
jval	6a736f6e	NO
group_virtual	766172636861722832353029	NO		VIRTUAL GENERATED
user_virtual	766172636861722832353029	NO		VIRTUAL GENERATED

if table is already created and you want to create Generated Columns, then use the following syntax

ALTER TABLE
    `table1` ADD COLUMN `user_virtual` VARCHAR(250) 
     GENERATED ALWAYS AS(`jval` - >> '$.user') NOT NULL 
AFTER `jval`;

Dont forget to index the Generated Columns

CREATE INDEX `users_idx` ON `table1`(`user_virtual`);

Then try

SHOW INDEX FROM table1;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
table1	0	PRIMARY	1	id	A	0	null	null		BTREE			YES
table1	1	users_idx	1	user_virtual	A	0	null	null		BTREE			YES

db<>fiddle here

Thanks Indra for your answer. I have already created the table. So, can you please suggest what will be the `Alter statement` in this scenario? — meallhour, Jun 24 '21 at 15:35
Actually, having both `Create` and `Alter` statements would be beneficial for reference in future. — meallhour, Jun 24 '21 at 15:36
@meallhour Please check the updated answer. You need to do 2 things 1) generate a virtual column 2) And index the generated one — Indra Kumar S, Jun 24 '21 at 16:07

How to create a virtual column to index JSON column in MySQL?

1 Answers1

Linked