JSON_REMOVE for array.array results in "path expressions may not contain the * and ** tokens" Error

Question

If I do: SELECT JSON_REMOVE(@I, '$.friends[*].name'); OR SELECT JSON_REMOVE(@I, '$.friends[*].friends'); on the JSON below, I get this this error:

ERROR 3149 (42000): In this situation, path expressions may not contain the * and ** tokens.

JSON:

SET @I = '{
  "name": "Alice",
  "friends": [
    {
      "name": "Bob",
      "friends": [
        {
          "name": "Carl",
          "friends": []
        },
        {
          "name": "Danny",
          "friends": []
        }
      ]
    },
    {
      "name": "Edward",
      "friends": [
        {
          "name": "Frank",
          "friends": []
        },
        {
          "name": "Gary",
          "friends": []
        }
      ]
    }
  ]
}';

However if I do SELECT JSON_EXTRACT(@I, '$.friends[*].friends') it returns the results fine.

[[{"friends": [], "name": "Carl"}, {"friends": [], "name": "Danny"}], [{"name": "Frank", "friends": []}, {"name": "Gary", "friends": []}]]

Basically I want to return a string with all friends.name removed and maybe even friends.friends removed.

score 0 · Answer 1 · answered Sep 13 '17 at 07:09

It looks like MySQL does not support wildcards for such functions as json_remove, json_set etc.

bool Item_func_json_remove::val_json(Json_wrapper *wr)
{
    //bla-bla-bla
    if (m_path_cache.parse_and_cache_path(args, path_idx + 1, true))
}

and

bool Json_path_cache::parse_and_cache_path(Item ** args, uint arg_idx,
                                           bool forbid_wildcards)

https://github.com/mysql/mysql-server/blob/5.7/sql/item_json_func.cc#L3227 https://github.com/mysql/mysql-server/blob/5.7/sql/item_json_func.cc#L2563 https://github.com/mysql/mysql-server/blob/5.7/sql/item_json_func.cc#L534

JSON_REMOVE for array.array results in "path expressions may not contain the * and ** tokens" Error

1 Answers1