How to create indexing for json in mysql?

Question

i have the update version mysql as

select version()

return 10.4.11-MariaDB

I able to create json type table but not able to create indexing in mysql

CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
);

i run this command its return

1 queries executed, 0 success, 1 errors, 0 warnings

Query:

CREATE TABLE `players` (
  `id` INT UNSIGNED NOT NULL,
  `player_and_games` JSON NOT NULL,
  `names_virtual` VARCHAR(20) GENERATED ALW...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
)' at line 4

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

I also used mysql offical documentation it also return the same issue.

sticky bit · Accepted Answer · 2020-02-03 13:04:15Z

1

Try to use json_value(). And a generated column cannot be declared NOT NULL apparently.

CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (json_value(`player_and_games`, '$.name')), 
   PRIMARY KEY (`id`)
);

answered Feb 3, 2020 at 13:04

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

3 Comments

jackson smith Over a year ago

this is working fine ; but why official documentation query is not working?

sticky bit Over a year ago

What "official documentation" are you referring to?

sticky bit Over a year ago

Care to give more detail? But anyway this is the MySQL documentation and you use MariaDB. Both share the same heritage and therefore might be similar, but they're not the same in every aspect.

Rick James · Accepted Answer · 2020-02-08 22:49:50Z

1

Be careful -- MySQL and MariaDB are not in lock-step over JSON. In particular, the >> operator exists in one but not the other. (It is just a shorthand for something slightly more verbose.)

answered Feb 8, 2020 at 22:49

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

Bo Finnerup Madsen · Accepted Answer · 2021-03-23 11:23:08Z

0

I know I am late to the party, but none the less. You are getting this error because you are running MariaDB and not MySQL. They are mostly interchangeable, but their json implementations are a bit different. MariaDB does not support the ->> syntax for extracting values from json documents, instead you need to use json_unquote(json_value(, )).

You can see the list of json functions in MariaDB here: https://mariadb.com/kb/en/json-functions/

answered Mar 23, 2021 at 11:23

Bo Finnerup Madsen

315 bronze badges

Collectives™ on Stack Overflow

How to create indexing for json in mysql?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related