MariaDB JSON remove key and its values

Question

I have a TABLBE like

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'

AND INSERT

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"}} 
               
               ]'  
    ) ;

SELECT*

---------------------------------------------------
`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"}}
         |         ]
---------------------------------------------------------------------------------------------    

     

I would like to REMOVE "papa@gmail_DOT_com" and all his values from AABBCC

I have tried (Am using 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'  

This returns

 ---------------------------------------------------
    `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"}}]

Is there a way to avoid having the empty {} after removal?

UPDATE01- If you try:

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

This deletes all data in the column user_data_json WHERE link_id= 'AABBCC'`

Thank you

Answer 1

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

will return:

[{"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"}}]

I am not really using JSON, but got my inspiration from the second example here: https://mariadb.com/kb/en/json_remove/

EDIT:

You could optimize this:

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;

output:

+---------------+---------+------+----------------------------------------------+
| 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                                         |
+---------------+---------+------+----------------------------------------------+

With this you could 'convert' "papa@gm...." to 1.

EDIT2: Combining different JSON functions from Mariadb or from MySQL can do a lot:

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
                  

output (please scroll right, the last column is more interesting than the first column ):

+ ----------- + ------------------------ + --------------------------------------------- + ----------------------------------------------------------- + ------------------------------------------- +
| 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                           |
+ ----------- + ------------------------ + --------------------------------------------- + ----------------------------------------------------------- + ------------------------------------------- +

EDIT (2020-12-26): I did have a look at mariadb, and below is tested on version 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"                                                  |
+-----------------------------------------------------------------------+

But use of $[1] is not desired, soe whe have to determine the correct value for 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 is the correct value for finding 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"}}] 

Answer 2

I've played some time with this puzzle and I figured in another way to do it. You can use json_search (plus to other functions) to finally use json_remove. Once you a creating an array of jsons, we must consider it are your designer decision to upload data as is. So, this is my code:

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'

1. json_search(sl.user_data_json,'one','papa@gmail_DOT_com')

   • Returns "$[1].papa@gmail_DOT_com.u_email"

2. JSON_UNQUOTE

   • Returns $[1].papa@gmail_DOT_com.u_email

3. SUBSTRING_INDEX(@JSON,'.',1)

   • Returns $[1]

4. And finally you will use this last return as JSON_REMOVE path.

I don't know if your JSON key will be always the same of u_email but if it's true, then you can use it.