Блог

как избежать двойных кавычек и скобок при запросе столбца JSON, содержащего массив объектов?

Это работает, когда я его запускаю…

 SELECT boarded, boarded->>"$[0].division.id" AS divisionId FROM onboarder
  

Но это не так…

 SELECT boarded, boarded->>"$[*].division.id" AS divisionId FROM onboarder
  

Я думал, что двойные стрелки экранировали все и вернули только значение. Это то, что у меня есть…

->> Оператор ничего не экранирует. Он просто преобразует результат в скалярный тип данных, такой как text или integer . Это эквивалентно выполнению JSON_UNQUOTE(JSON_EXTRACT(…)):

Выводит значение JSON из кавычек и возвращает результат в виде строки utf8mb4.

Мы можем продемонстрировать разницу между -> и ->> , используя набор результатов для создания новой таблицы и проверяя типы данных, которые она создает.

 create table t as SELECT boarded, boarded->"$[*].division.id" AS divisionId FROM onboarder;

show create table t;
CREATE TABLE `t` (
  `boarded` json DEFAULT NULL,
  `divisionId` json DEFAULT NULL
);

select * from t;
 ------------------------------------ -------------- 
| boarded                            | divisionId   |
 ------------------------------------ -------------- 
| [{"division": {"id": "8ac7a..."}}] | ["8ac7a..."] |
 ------------------------------------ -------------- 
  

Обратите внимание, что divisionId — это документ json, представляющий собой массив.

Если мы используем ->> это то, что происходит:

 create table t as SELECT boarded, boarded->>"$[*].division.id" AS divisionId FROM onboarder;

show create table t;

CREATE TABLE `t` (
  `boarded` json DEFAULT NULL,
  `divisionId` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
)

select * from t;
 ------------------------------------ -------------- 
| boarded                            | divisionId   |
 ------------------------------------ -------------- 
| [{"division": {"id": "8ac7a..."}}] | ["8ac7a..."] |
 ------------------------------------ -------------- 
  

Видимой разницы нет, потому что квадратные скобки все еще присутствуют. Но последний хранится как longtext тип данных.

Повторите свой комментарий:

Как я могу вернуть divisionId в качестве значения, чтобы оно не было массивом и не заключалось в кавычки?

Вы использовали $[*] в своем запросе, и смысл этого шаблона заключается в том, чтобы возвращать все элементы массива в виде массива. Чтобы получить единственное значение, вам нужно запросить один элемент массива, как в вашем первом примере:

 boarded->>'$[0].division.id'
  

Это было бы намного проще, если бы вы не использовали JSON, а хранили свои данные в традиционной таблице с одним разделением в отдельной строке и каждым полем разделения в своем собственном столбце.

 CREATE TABLE divisions (
  division_id VARCHAR(...) PRIMARY KEY,
  merchant ...
);

CREATE TABLE merchants (
  merchant_id ... PRIMARY KEY,
  division_id VARCHAR(...),
  FOREIGN KEY (division_id) REFERENCES division(division_id)
);
  

Чем больше примеров я вижу того, как разработчики пытаются использовать JSON в MySQL, тем больше я убежден, что это плохая идея.