Как извлечь уникальные имена вложенных переменных из одной строковой переменной?
Дело
В нашей базе данных MySql данные хранятся в комбинированных json-строках следующим образом:
| ID | DATA |
| 100 | {var1str: "sometxt", var2double: 0,01, var3integer: 1, var4str: "another text"} |
| 101 | {var3integer: 5, var2double: 2,05, var1str: "txt", var4str: "more text"} |
Проблема
Большинство полей данных содержат более 2500 переменных. Порядок переменных в строке DATA является случайным (как показано в примере выше). Сейчас мы знаем только, как извлечь данные с помощью следующего запроса:
select
ID,
json_extract(DATA,'var1str'),
json_extract(DATA,'var2double'),
FROM table
С помощью этого запроса в качестве результата будут возвращены только значения var1str и var2double. Значения переменных 3 и 4 игнорируются. Нет обзора того, какие возможные переменные скрываются в полях данных.
Имея почти 60 000 записей и более 3 000 возможных уникальных имен переменных, я хотел бы создать запрос, который просматривает все 60 000 полей DATA и извлекает каждое уникальное имя переменной, которое там находится.
Решение?
Запрос, который я ищу, даст следующий результат:
var1str
var2double
var3integer
var4str
Мои знания MySql очень ограничены. Мы очень ценим любое указание на это решение.
Спасибо за ответ. Я немного заглянул в хранимые процедуры, думаю, в HeidiSQL это возможно. Будет ли это то же самое, что и то, что вы называете «анализом данных на клиентском языке»?
Да, хранимая процедура должна быть способной, но клиентские языки, как правило, имеют лучшие библиотеки (например, стандартные методы «разделенной строки») для облегчения подобных вещей.
Ответы 1
Какую версию MySQL вы используете?
Начиная с MySQL 8.0.4 и более поздних версий функция JSON_TABLE поддерживается и может быть полезна в этом случае.
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.11 |
+-----------+
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS `table`;
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE IF NOT EXISTS `table` (
-> `ID` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> `DATA` JSON NOT NULL
-> ) AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `table`
-> (`DATA`)
-> VALUES
-> ('{"var1str": "sometxt", "var2double": 0.01, "var3integer": 1, "var4str": "another text"}'),
-> ('{"var3integer": 5, "var2double": 2.05, "var1str": "txt", "var4str": "more text"}');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT
-> DISTINCT `der`.`key`
-> FROM
-> `table`,
-> JSON_TABLE(
-> JSON_KEYS(`DATA`), '$[*]'
-> COLUMNS(
-> `key` VARCHAR(64) PATH "$"
-> )
-> ) `der`;
+-------------+
| key |
+-------------+
| var1str |
| var4str |
| var2double |
| var3integer |
+-------------+
4 rows in set (0.01 sec)
Помните о Ошибка № 90610 ОШИБКА 1142 (42000) при использовании JSON_TABLE.
Я не думаю, что есть ни один запрос, который может сделать это за вас; хранимая процедура могла бы, но в этом случае вам может быть лучше просто проанализировать данные на вашем клиентском языке.