Ставить MySQL 8.X на Fedory 35 - не лучшая идея.
Первые 6 записей вставляю по документу.
=======================================
MariaDB [verify]> create database datajs ;
Query OK, 1 row affected (0.001 sec)
MariaDB [verify]> use datajs ;
Database changed
MariaDB [datajs]> CREATE TABLE locations (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(100) NOT NULL,
-> type CHAR(1) NOT NULL,
-> latitude DECIMAL(9,6) NOT NULL,
-> longitude DECIMAL(9,6) NOT NULL,
-> attr JSON,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.010 sec)
MariaDB [datajs]> desc locations ;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| type | char(1) | NO | | NULL | |
| latitude | decimal(9,6) | NO | | NULL | |
| longitude | decimal(9,6) | NO | | NULL | |
| attr | longtext | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)
MariaDB [datajs]> INSERT INTO locations (type, name, latitude, longitude, attr) VALUES
-> ('R', 'Lou Malnatis', 42.0021628, -87.7255662,
-> '{"details": {"foodType": "Pizza", "menu":
'> "favorites": [{"description": "Pepperoni deep dish", "price": 18.75},
'> {"description": "The Lou", "price": 24.75}]}');
Query OK, 1 row affected, 2 warnings (0.001 sec)
=================
MariaDB [datajs]> INSERT INTO locations (type, name, latitude, longitude, attr) VALUES
-> ('A', 'Cloud Gate', 41.8826572, -87.6233039,
-> '{"category": "Landmark", "lastVisitDate": "11/10/2019"}');
Query OK, 1 row affected, 2 warnings (0.001 sec)
MariaDB [datajs]> SELECT name, latitude, longitude,
-> JSON_VALUE(attr, '$.details.foodType') AS food_type
-> FROM locations
-> WHERE type = 'R';
+--------------+-----------+------------+-----------+
| name | latitude | longitude | food_type |
+--------------+-----------+------------+-----------+
| Lou Malnatis | 42.002163 | -87.725566 | Pizza |
+--------------+-----------+------------+-----------+
1 row in set (0.001 sec)
===================
MariaDB [datajs]> SELECT name, latitude, longitude,
-> JSON_VALUE(attr, '$.details.foodType') AS food_type
-> FROM locations ;
+--------------+-----------+------------+-----------+
| name | latitude | longitude | food_type |
+--------------+-----------+------------+-----------+
| Lou Malnatis | 42.002163 | -87.725566 | Pizza |
| Cloud Gate | 41.882657 | -87.623304 | NULL |
+--------------+-----------+------------+-----------+
2 rows in set (0.001 sec)
==================
Index Creating
==================
MariaDB [datajs]> ALTER TABLE locations ADD COLUMN
-> food_type VARCHAR(25) AS (JSON_VALUE(attr, '$.details.foodType')) VIRTUAL;
Query OK, 0 rows affected (0.009 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [datajs]> CREATE INDEX foodtypes ON locations(food_type);
Query OK, 0 rows affected (0.008 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [datajs]> select id from locations;
+----+
| id |
+----+
| 2 |
| 1 |
+----+
2 rows in set (0.001 sec)
MariaDB [datajs]> UPDATE locations
-> SET attr = JSON_INSERT(attr,'$.nickname','The Bean')
-> WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [datajs]> UPDATE locations
-> SET attr = JSON_INSERT(attr,
-> '$.foodTypes',
-> JSON_ARRAY('Asian', 'Mexican'))
-> WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
========================================
[boris@fedora35server ~]$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.5.13-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
========================================================
MariaDB [(none)]> use datajs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [datajs]> desc locations;
+-----------+--------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| type | char(1) | NO | | NULL | |
| latitude | decimal(9,6) | NO | | NULL | |
| longitude | decimal(9,6) | NO | | NULL | |
| attr | longtext | YES | | NULL | |
| food_type | varchar(25) | YES | MUL | NULL | VIRTUAL GENERATED |
+-----------+--------------+------+-----+---------+-------------------+
7 rows in set (0.002 sec)
MariaDB [datajs]> SELECT name, latitude, longitude,
-> JSON_VALUE(attr, '$.details.foodType') AS food_type
-> FROM locations
-> WHERE type = 'R';
+--------------+-----------+------------+-----------+
| name | latitude | longitude | food_type |
+--------------+-----------+------------+-----------+
| Lou Malnatis | 42.002163 | -87.725566 | Pizza |
+--------------+-----------+------------+-----------+
1 row in set (0.001 sec)
MariaDB [datajs]> SELECT name, latitude, longitude,
-> JSON_VALUE(attr, '$.details.foodType') AS food_type
-> FROM locations
-> ;
+--------------+-----------+------------+-----------+
| name | latitude | longitude | food_type |
+--------------+-----------+------------+-----------+
| Lou Malnatis | 42.002163 | -87.725566 | Pizza |
| Cloud Gate | 41.882657 | -87.623304 | NULL |
+--------------+-----------+------------+-----------+
2 rows in set (0.001 sec)
MariaDB [datajs]> select * from locations ;
+----+--------------+------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| id | name | type | latitude | longitude | attr | food_type |
+----+--------------+------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| 1 | Lou Malnatis | R | 42.002163 | -87.725566 | {"details": {"foodType": "Pizza", "menu": "
https://www.loumalnatis.com/our-menu"}, "favorites": [{"description": "Pepperoni deep dish", "price": 18.75}, {"description": "The Lou", "price": 24.75}], "nickname": "The Bean", "foodTypes": ["Asian", "Mexican"]} | Pizza |
| 2 | Cloud Gate | A | 41.882657 | -87.623304 | {"category": "Landmark", "lastVisitDate": "11/10/2019"} | NULL |
+----+--------------+------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
2 rows in set (0.001 sec)