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": "[email protected]", "user": "user1"}

{"group": "[email protected]", "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
– Indra Kumar S, Commented Jun 24, 2021 at 15:11
Here jval will have 2 values as below: {[email protected], sme1} {[email protected], sme2} — meallhour
– meallhour, Commented Jun 24, 2021 at 15:15
Its not a valid json. Just share a proper json and the field to be indexed — Indra Kumar S
– Indra Kumar S, Commented Jun 24, 2021 at 15:16

Indra Kumar S · Accepted Answer · 2021-06-24 16:16:11Z

15

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

edited Jun 24, 2021 at 16:16

answered Jun 24, 2021 at 15:33

Indra Kumar S

2,9552 gold badges19 silver badges29 bronze badges

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

5 Comments

meallhour Over a year ago

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 Over a year ago

Actually, having both Create and Alter statements would be beneficial for reference in future.

Indra Kumar S Over a year ago

@meallhour Please check the updated answer. You need to do 2 things 1) generate a virtual column 2) And index the generated one

Nuno Over a year ago

Hey Indra! Does this mean that the index basically "caches" the results of the VIRTUAL expression, and when I query the index, it won't run the expression for all rows every time? And when is the index updated? Is it only when we run an UPDATE on that row? Many thanks!

Nuno Over a year ago

Actually, my answer is here: dev.mysql.com/blog-archive/… "The index itself is now material and it does exist (as all other indexes do)." (referring to an index on a VIRTUAL column)

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related