SQL-запрос для обновления значения поля JSON в MySQL
Используя MySQL, мне нужно обновить запись, в которой в поле типа json есть нулевое значение. Например,
Таблица 1
Таблица 2
Я написал этот запрос, чтобы сначала получить записи, в которых в ключе имени хранится нулевое значение.
SELECT *
FROM table 1
WHERE JSON_CONTAINS(column B, '{"name": null}', '$');
Теперь мне нужно обновить записи с помощью SQL-запроса, чтобы удалить объект из массива в столбце B таблицы 1. Таблице 1 должно понравиться это.
Таблица 1
Как я могу добиться этого без написания хранимых процедур?
Я попытался обновить одну запись, используя этот запрос
UPDATE table 1
SET column_B = JSON_ARRAY(
JSON_OBJECT(
"id", JSON_UNQUOTE(JSON_EXTRACT(column_B, '$[0].id')),
"name", JSON_UNQUOTE(JSON_EXTRACT(injuries, '$[0].name'))
),
)
WHERE id = 1;
Смотрите мой ответ здесь: stackoverflow.com/a/71743929/20860
table2 — это основная таблица, из которой данные извлекаются для хранения в столбце B table1, и да, если ячейка содержит несколько значений с нулевым именем, все они должны быть удалены, а если в ячейке есть только один объект с именем null, весь столбец будет нулевым для ряд.
После долгих исследований и расследований я нашел решение... Пожалуйста, проверьте @tech_learner
Ответы 1
Существует функция JSON JSON_TABLE, которая используется для преобразования каждого массива JSON в количество объектов (строк) JSON, содержащихся в этом массиве JSON. Пусть, если массив JSON свяжется с объектом n JSON, он создаст n строки для каждого объекта, который существует в этом массиве JSON.
Например:
Давайте, мой вклад:
Я хочу сломать следующее:
set @jsonArray = JSON_ARRAY(
JSON_OBJECT('key', 1, 'value', 'Data1'),
JSON_OBJECT('key', 2, 'value', 'Data2'),
JSON_OBJECT('key', 3, 'value', 'Data3')
);
select *
from JSON_TABLE(
@jsArray, '$[*]' COLUMNS (
split_json_array JSON PATH '$'
)
) AS JT
Аналогичным образом нам нужно преобразовать все значения массива JSON «Столбец B» в соответствующий формат. Затем нам нужно применить условие, split_json_array->>'$.name' = 'null' отфильтровать элементы JSON-массива «Столбец B», имя которых — выходы, а затем объединить эти данные.
Итак, соответствующий запрос:
WITH Base_Table_1 AS (
select * from
Table_1,
JSON_TABLE(column_B, '$[*]' COLUMNS (
split_json_array JSON PATH '$'
)) AS jt
),
filter_Table_1 AS (
SELECT
column_A,
JSON_ARRAYAGG(split_json_array) AS column_B_filtered_data
from Base_Table_1
where split_json_array->>'$.name' != 'null'
group by column_A
)
Select * from filter_Table_1;
Вывод:
Затем обновите значение Table_1 таблицы column_B из таблицы filter_Table_1:
UPDATE Table_1
SET column_B = (
select filter_Table_1.column_B_filtered_data
From filter_Table_1
WHERE Table_1.column_A = filter_Table_1.column_A
);
Итак, последний Table_1 отображается как:
select * from Table_1;
Пример кода для живого тестирования: myCompiler
Для этого требуется много исследований и исследований, и я узнаю кое-что новое, связанное с обработкой данных MySQL JSON.
Я надеюсь, что ваша проблема разрешится.
Спасибо за этот вопрос.
Какую цель имеет таблица2? Вы не используете его в своем sql. Если ячейка содержит несколько или даже только имена с
NULL, следует ли удалить их все, даже если при этом будет выделен весь столбецNULLдля этой строки?