CODEWITHSUNDEEP
jsonmariadb
jackson smith
jackson smith

Reputation: 65

How to create indexing for json in mysql?

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.

Upvotes: 0

Views: 447

Answers (3)

Bo Finnerup Madsen
Bo Finnerup Madsen

Reputation: 31

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/

Upvotes: 0

Rick James
Rick James

Reputation: 142298

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.)

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

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`)
);

Upvotes: 1

Related Questions