MariaDB JSON удаляет ключ и его значения

ProgramBox

MariaDB JSON удаляет ключ и его значения

Post author:admin
Запись опубликована:30 октября, 2022
Post category:Вопросы по программированию

#mariadb #key #mysql-json #mariadb-10.4

#mariadb #Клавиша #mysql-json #mariadb-10.4

Вопрос:

У меня есть таблица, подобная

 CREATE TABLE `saved_links` (
 `link_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `link_id` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
 `user_data_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`link_entry_id`),
 UNIQUE KEY `link_id` (`link_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='saved Links'

И ВСТАВЛЯЕТ

 INSERT INTO `saved_links`(`link_id`, `user_data_json` ) 
VALUES ( 
        'AABBCC',  
        '[{
            "mama@gmail_DOT_com": {"u_email": "mama@gmail_DOT_com", "private": "no"}}, 
          {
           "papa@gmail_DOT_com": {"u_email": "papa@gmail_DOT_com", "private": "no"}},
          {
           "daughter@gmail_DOT_com": {"u_email": "daughter@gmail_DOT_com", "private": "no"}},
          {
           "son@gmail_DOT_com": {"u_email": "son@gmail_DOT_com", "private": "no"}
        }]'  
    ), ( 
        'DDEEFF',  
            '[{
               "mama@gmail_DOT_com": {"u_email": "mama@gmail_DOT_com", "private": "no"}}, 
              {
               "papa@gmail_DOT_com": {"u_email": "papa@gmail_DOT_com", "private": "no"}} 
               
               ]'  
    ) ;

ВЫБЕРИТЕ*

 ---------------------------------------------------
`link_id` | `user_data_json` 
----------------------------------------------------
`AABBCC` | [{
         |         "mama@gmail_DOT_com": {"u_email": "mama@gmail_DOT_com", "private": "no"}}, 
         |         {
         |          "papa@gmail_DOT_com": {"u_email": "papa@gmail_DOT_com", "private": "no"}},
         |         {
         |          "daughter@gmail_DOT_com": {"u_email": "daughter@gmail_DOT_com", "private": "no"}},
         |         {
         |          "son@gmail_DOT_com": {"u_email": "son@gmail_DOT_com", "private": "no"}}]
---------------------------------------------------------------------------------------------  
`DDEEFF` | [{
         |         "mama@gmail_DOT_com": {"u_email": "mama@gmail_DOT_com", "private": "no"}}, 
         |         {
         |          "papa@gmail_DOT_com": {"u_email": "papa@gmail_DOT_com", "private": "no"}}
         |         ]
---------------------------------------------------------------------------------------------

Я хотел бы удалить "papa@gmail_DOT_com" и все его values из AABBCC

Я пробовал (использую 10.4.15-MariaDB)

     UPDATE `saved_links` 
      SET `user_data_json` = IFNULL( 
        JSON_REMOVE( `user_data_json`,  JSON_UNQUOTE( 
            REPLACE( JSON_SEARCH(
                `user_data_json`, 'all', 'papa@gmail_DOT_com', NULL, '$**.papa@gmail_DOT_com'), '.u_email', '' ) ) ), `user_data_json` )
 where `link_id` = 'AABBCC'

Это возвращает

  ---------------------------------------------------
    `link_id` | `user_data_json` 
    ----------------------------------------------------
    `AABBCC` | [{
             |         "mama@gmail_DOT_com": {"u_email": "mama@gmail_DOT_com", "private": "no"}}, 
             |         {}, //-> Notice these empty braces that are left behind.
             |         {
             |          "daughter@gmail_DOT_com": {"u_email": "daughter@gmail_DOT_com", "private": "no"}},
             |         {
             |          "son@gmail_DOT_com": {"u_email": "son@gmail_DOT_com", "private": "no"}}]

Есть ли способ избежать пустоты {} после удаления?

ОБНОВЛЕНИЕ01- Если вы попытаетесь:

 UPDATE `saved_links` SET 
`user_data_json` = 
      JSON_REMOVE(`user_data_json`, '$.papa@gmail_DOT_com') 
 WHERE  `link_id`= 'AABBCC'

При этом удаляются все данные в столбце user_data_json , ГДЕ link_id = ‘AABBCC»

Спасибо

1. использовать JSON_REMOVE ?

2. Уже используется… посмотрите, что я пробовал в посте

3. Я отредактировал теги, поскольку вы используете MariaDB, а не MySQL. Эти два продукта имеют разные реализации для данных JSON. Они не должны рассматриваться как один и тот же продукт базы данных.

4. @BillKarwin Спасибо. Хотя я все еще застрял. Дайте мне знать, если у вас есть какие-либо мысли

Ответ №1:

select json_remove(user_data_json,'$[1]') from saved_links where link_entry_id=19;

вернет:

 [{"mama@gmail_DOT_com": {"private": "no", "u_email": "mama@gmail_DOT_com"}},
 {"daughter@gmail_DOT_com": {"private": "no", "u_email": "daughter@gmail_DOT_com"}},
 {"son@gmail_DOT_com": {"private": "no", "u_email": "son@gmail_DOT_com"}}]

На самом деле я не использую JSON, но вдохновился вторым примером здесь: https://mariadb.com/kb/en/json_remove /

Редактировать:

Вы могли бы оптимизировать это:

 with recursive abc as (
  Select 0 as i 
  union all 
  select i 1 from abc where i<2) 
select link_entry_id, link_id,i, json_keys(user_data_json,concat('$[',i,']')) 
from saved_links,abc;

вывод:

  --------------- --------- ------ ---------------------------------------------- 
| link_entry_id | link_id | i    | json_keys(user_data_json,concat('$[',i,']')) |
 --------------- --------- ------ ---------------------------------------------- 
|            19 | AABBCC  |    0 | ["mama@gmail_DOT_com"]                       |
|            20 | DDEEFF  |    0 | ["mama@gmail_DOT_com"]                       |
|            19 | AABBCC  |    1 | ["papa@gmail_DOT_com"]                       |
|            20 | DDEEFF  |    1 | ["papa@gmail_DOT_com"]                       |
|            19 | AABBCC  |    2 | ["daughter@gmail_DOT_com"]                   |
|            20 | DDEEFF  |    2 | NULL                                         |
 --------------- --------- ------ ----------------------------------------------

С помощью этого вы могли бы «преобразовать» "papa@gm...." в 1 .

РЕДАКТИРОВАНИЕ 2: объединение различных функций JSON из Mariadb или MySQL может многое сделать:

 SELECT 
   j.person,
   JSON_KEYS(j.person), 
   JSON_EXTRACT(JSON_KEYS(j.person),'$[0]'), 
   JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(j.person),'$[0]')),
   JSON_VALUE(JSON_KEYS(j.person),'$[0]')
FROM 
   JSON_TABLE('[{
            "mama@gmail_DOT_com": {"u_email": "mama@gmail_DOT_com", "private": "no"}}, 
          {
           "papa@gmail_DOT_com": {"u_email": "papa@gmail_DOT_com", "private": "no"}},
          {
           "daughter@gmail_DOT_com": {"u_email": "daughter@gmail_DOT_com", "private": "no"}},
          {
           "son@gmail_DOT_com": {"u_email": "son@gmail_DOT_com", "private": "no"}
        }]', 
                  '$[*]' COLUMNS(person JSON PATH '$[0]')) j

вывод (пожалуйста, прокрутите вправо, последний столбец более интересен, чем первый столбец 😉):

   -----------   ------------------------   ---------------------------------------------   -----------------------------------------------------------   -------------------------------------------  
| person      | JSON_KEYS(j.person)      | JSON_EXTRACT(JSON_KEYS(j.person),'$[0]')      | JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(j.person),'$[0]'))      | JSON_VALUE(JSON_KEYS(j.person),'$[0]')      |
  -----------   ------------------------   ---------------------------------------------   -----------------------------------------------------------   -------------------------------------------  
| {"mama@gmail_DOT_com": {"private": "no", "u_email": "mama@gmail_DOT_com"}} | ["mama@gmail_DOT_com"]   | "mama@gmail_DOT_com"                          | mama@gmail_DOT_com                                          | mama@gmail_DOT_com                          |
| {"papa@gmail_DOT_com": {"private": "no", "u_email": "papa@gmail_DOT_com"}} | ["papa@gmail_DOT_com"]   | "papa@gmail_DOT_com"                          | papa@gmail_DOT_com                                          | papa@gmail_DOT_com                          |
| {"daughter@gmail_DOT_com": {"private": "no", "u_email": "daughter@gmail_DOT_com"}} | ["daughter@gmail_DOT_com"] | "daughter@gmail_DOT_com"                      | daughter@gmail_DOT_com                                      | daughter@gmail_DOT_com                      |
| {"son@gmail_DOT_com": {"private": "no", "u_email": "son@gmail_DOT_com"}} | ["son@gmail_DOT_com"]    | "son@gmail_DOT_com"                           | son@gmail_DOT_com                                           | son@gmail_DOT_com                           |
  -----------   ------------------------   ---------------------------------------------   -----------------------------------------------------------   -------------------------------------------

РЕДАКТИРОВАТЬ (2020-12-26):
Я посмотрел на mariadb, и ниже протестирована версия 10.5.8 .

 select json_extract(json_array(user_data_json,"papa@gmail_DOT_com"), '$[1]') from saved_links;
 ----------------------------------------------------------------------- 
| json_extract(json_array(user_data_json,"papa@gmail_DOT_com"), '$[1]') |
 ----------------------------------------------------------------------- 
| "papa@gmail_DOT_com"                                                  |
| "papa@gmail_DOT_com"                                                  |
 -----------------------------------------------------------------------

Но использование нежелательно, поэтому необходимо определить правильное значение для $[1] 1 :

 WITH RECURSIVE data AS (
  SELECT 
    link_entry_id, 
    link_id, 
    0 as I, 
    JSON_KEYS(user_data_json, '$[0]') jk
  FROM saved_links
  UNION ALL
  SELECT 
    sl.link_entry_id, 
    sl.link_id, 
    I 1, 
    JSON_KEYS(user_data_json, CONCAT('$[',i 1,']')) 
  FROM saved_links sl, (select max(i) as I from data) x
  WHERE JSON_KEYS(user_data_json, CONCAT('$[',i 1,']'))<>'')
SELECT * FROM data
;

  --------------- --------- ------ ---------------------------- 
| link_entry_id | link_id | I    | jk                         |
 --------------- --------- ------ ---------------------------- 
|            19 | AABBCC  |    0 | ["mama@gmail_DOT_com"]     |
|            20 | DDEEFF  |    0 | ["mama@gmail_DOT_com"]     |
|            19 | AABBCC  |    1 | ["papa@gmail_DOT_com"]     |
|            20 | DDEEFF  |    1 | ["papa@gmail_DOT_com"]     |
|            19 | AABBCC  |    2 | ["daughter@gmail_DOT_com"] |
|            19 | AABBCC  |    3 | ["son@gmail_DOT_com"]      |
 --------------- --------- ------ ----------------------------

I является правильным значением для поиска papa@gmail_DOT_com

 WITH RECURSIVE data AS (
  SELECT 
    link_entry_id, 
    link_id, 
    0 as I, 
    JSON_KEYS(user_data_json, '$[0]') jk
  FROM saved_links
  UNION ALL
  SELECT 
    sl.link_entry_id, 
    sl.link_id, 
    I 1, 
    JSON_KEYS(user_data_json, CONCAT('$[',i 1,']')) 
  FROM saved_links sl, (select max(i) as I from data) x
  WHERE JSON_KEYS(user_data_json, CONCAT('$[',i 1,']'))<>'')
SELECT 
   json_remove(user_data_json, concat('$[',I,']'))
FROM saved_links sl 
INNER JOIN data d ON d.link_entry_id= sl.link_entry_id AND d.link_id=sl.link_id and d.I=1
;

 [{"mama@gmail_DOT_com": {"u_email": "mama@gmail_DOT_com", "private": "no"}},
      {"daughter@gmail_DOT_com": {"u_email": "daughter@gmail_DOT_com", "private": "no"}}, 
      {"son@gmail_DOT_com": {"u_email": "son@gmail_DOT_com", "private": "no"}}] 

[{"mama@gmail_DOT_com": {"u_email": "mama@gmail_DOT_com", "private": "no"}}]

1. Спасибо, но я избегаю использования '$[N]' индексов, потому что записи не поступают в предсказуемом порядке… первое электронное письмо может son@gmail_DOT_com быть, второе может быть grandpa@gmail_DOT_com так, что если вы удалите на основе индекса массива, вы можете удалить неправильную запись, потому что они вставляются случайным образом

2.Выглядит хорошо, но PHP мне нужно передать $email в MySql Query …. Есть идеи, как передать PHP $email variable использование этого рекурсивного метода в MySql query ?

3. Я добавил EDIT2 . Другим способом может быть создание функции , и пусть она обрабатывает JSON-материал.

4. Спасибо за усилия. Не могли бы вы предоставить ссылку на скрипку, чтобы проверить ??… Еще раз спасибо

5. скрипка

Ответ №2:

Я некоторое время играл с этой головоломкой и придумал другой способ сделать это. Вы можете использовать json_search (плюс другие функции), чтобы, наконец, использовать json_remove. Как только вы создаете массив json, мы должны учитывать, что это ваше дизайнерское решение загружать данные как есть. Итак, это мой код:

 UPDATE saved_links sl 
SET user_data_json = 
JSON_REMOVE(user_data_json, 
    SUBSTRING_INDEX( 
        JSON_UNQUOTE( 
            JSON_SEARCH(sl.user_data_json,'one','papa@gmail_DOT_com') 
        )
    ,'.', 1) 
)
WHERE link_id='AABBCC'

json_search(sl.user_data_json,'one','papa@gmail_DOT_com')
- ВОЗВРАТ "$[1].papa@gmail_DOT_com.u_email"
JSON_UNQUOTE
- ВОЗВРАТ $[1].papa@gmail_DOT_com.u_email
SUBSTRING_INDEX(@JSON,'.',1)
- ВОЗВРАТ $[1]
И, наконец, вы будете использовать этот последний возврат в качестве пути JSON_REMOVE.

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