MDEV-22224 Support JSON Path negative index.bb-10.8-mdev22224-hf

When we see the negative index in the path, we do extra parsing to
calculate the size of the array.

7 files changed, 612 insertions, 66 deletions

diff --git a/include/json_lib.h b/include/json_lib.h
index 6d802860cff..de3bc1a47ec 100644
--- a/include/json_lib.h
+++ b/include/json_lib.h
@@ -80,6 +80,7 @@ enum json_path_step_types
   JSON_PATH_KEY_OR_ARRAY=3,
   JSON_PATH_WILD=4, /* Step like .* or [*] */
   JSON_PATH_DOUBLE_WILD=8, /* Step like **.k or **[1] */
+  JSON_PATH_NEGATIVE_INDEX=16, /* Step like [-1] */
   JSON_PATH_KEY_WILD= 1+4,
   JSON_PATH_KEY_DOUBLEWILD= 1+8,
   JSON_PATH_ARRAY_WILD= 2+4,
@@ -93,7 +94,7 @@ typedef struct st_json_path_step_t
                                    /* see json_path_step_types */
   const uchar *key; /* Pointer to the beginning of the key. */
   const uchar *key_end;  /* Pointer to the end of the key. */
-  uint n_item;      /* Item number in an array. No meaning for the key step. */
+  int n_item;      /* Item number in an array. No meaning for the key step. */
 } json_path_step_t;
 
 
@@ -355,7 +356,7 @@ int json_skip_level_and_count(json_engine_t *j, int *n_items_skipped);
 */
 int json_find_path(json_engine_t *je,
                    json_path_t *p, json_path_step_t **p_cur_step,
-                   uint *array_counters);
+                   int *array_counters);
 
 
 typedef struct st_json_find_paths_t
@@ -364,7 +365,7 @@ typedef struct st_json_find_paths_t
   json_path_t *paths;
   uint cur_depth;
   uint *path_depths;
-  uint array_counters[JSON_DEPTH_LIMIT];
+  int array_counters[JSON_DEPTH_LIMIT];
 } json_find_paths_t;
 
 
@@ -419,12 +420,8 @@ int json_get_path_start(json_engine_t *je, CHARSET_INFO *i_cs,
 int json_get_path_next(json_engine_t *je, json_path_t *p);
 
 
-int json_path_parts_compare(
-        const json_path_step_t *a, const json_path_step_t *a_end,
-        const json_path_step_t *b, const json_path_step_t *b_end,
-        enum json_value_types vt);
 int json_path_compare(const json_path_t *a, const json_path_t *b,
-                      enum json_value_types vt);
+                      enum json_value_types vt, const int *array_sizes);
 
 int json_valid(const char *js, size_t js_len, CHARSET_INFO *cs);
 
diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result
index 29b996f3066..5174377915b 100644
--- a/mysql-test/main/func_json.result
+++ b/mysql-test/main/func_json.result
@@ -1401,3 +1401,223 @@ DROP TABLE t;
 #
 # End of 10.5 tests
 #
+#
+# Beginning of 10.8 Test
+#
+# MDEV-22224: Support JSON Path negative index
+#
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5);
+JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, [15, 5]], true], "B": {"C": 1}, "D": 2}
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5);
+JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, 5, [15]], true], "B": {"C": 1}, "D": 2}
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_CONTAINS(@json, '15', '$.A[-2][-1]');
+JSON_CONTAINS(@json, '15', '$.A[-2][-1]')
+1
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]');
+JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]')
+1
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_EXISTS(@json, '$.A[-2][-1]');
+JSON_EXISTS(@json, '$.A[-2][-1]')
+1
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_EXTRACT(@json, '$.A[-8][1]');
+JSON_EXTRACT(@json, '$.A[-8][1]')
+5
+SET @json= '[{"A": 1, "B": 2, "C": {"D": 3}},{"A": 1, "B": 2, "C": {"D": 3}}]';
+SELECT JSON_KEYS(@json, '$[-1].C');
+JSON_KEYS(@json, '$[-1].C')
+["D"]
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_LENGTH(@json, '$.A[-2][-3]');
+JSON_LENGTH(@json, '$.A[-2][-3]')
+2
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_QUERY(@json, '$.A[-8]');
+JSON_QUERY(@json, '$.A[-8]')
+[4, 5, 6]
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_REMOVE(@json, '$.A[-10]');
+JSON_REMOVE(@json, '$.A[-10]')
+{"A": [[1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2}
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_REPLACE(@json, '$.A[-1]', 4);
+JSON_REPLACE(@json, '$.A[-1]', 4)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], 4], "B": {"C": 1}, "D": 2}
+SET @json = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
+SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]');
+JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]')
+"$[2].x"
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_SET(@json, '$.A[-4]', 100);
+JSON_SET(@json, '$.A[-4]', 100)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, 100, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2}
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":123},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_VALUE(@json, '$.A[-2][-2].key1');
+JSON_VALUE(@json, '$.A[-2][-2].key1')
+123
+#
+# End of 10.8 Test
+#
diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test
index eb84d607430..fd429c578d4 100644
--- a/mysql-test/main/func_json.test
+++ b/mysql-test/main/func_json.test
@@ -894,3 +894,210 @@ DROP TABLE t;
 --echo # End of 10.5 tests
 --echo #
 
+--echo #
+--echo # Beginning of 10.8 Test
+--echo #
+--echo # MDEV-22224: Support JSON Path negative index
+--echo #
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5);
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5);
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_CONTAINS(@json, '15', '$.A[-2][-1]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_EXISTS(@json, '$.A[-2][-1]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_EXTRACT(@json, '$.A[-8][1]');
+
+SET @json= '[{"A": 1, "B": 2, "C": {"D": 3}},{"A": 1, "B": 2, "C": {"D": 3}}]';
+SELECT JSON_KEYS(@json, '$[-1].C');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_LENGTH(@json, '$.A[-2][-3]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_QUERY(@json, '$.A[-8]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_REMOVE(@json, '$.A[-10]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_REPLACE(@json, '$.A[-1]', 4);
+
+SET @json = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
+SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_SET(@json, '$.A[-4]', 100);
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":123},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_VALUE(@json, '$.A[-2][-2].key1');
+
+--echo #
+--echo # End of 10.8 Test
+--echo #
diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc
index fefb2a1c662..c1438cc7289 100644
--- a/sql/item_jsonfunc.cc
+++ b/sql/item_jsonfunc.cc
@@ -466,7 +466,7 @@ bool Item_func_json_exists::fix_length_and_dec()
 longlong Item_func_json_exists::val_int()
 {
   json_engine_t je;
-  uint array_counters[JSON_DEPTH_LIMIT];
+  int array_counters[JSON_DEPTH_LIMIT];
 
   String *js= args[0]->val_json(&tmp_js);
 
@@ -535,7 +535,7 @@ bool Json_path_extractor::extract(String *str, Item *item_js, Item *item_jp,
 {
   String *js= item_js->val_json(&tmp_js);
   int error= 0;
-  uint array_counters[JSON_DEPTH_LIMIT];
+  int array_counters[JSON_DEPTH_LIMIT];
 
   if (!parsed)
   {
@@ -796,11 +796,12 @@ bool Item_func_json_extract::fix_length_and_dec()
 
 
 static bool path_exact(const json_path_with_flags *paths_list, int n_paths,
-                       const json_path_t *p, json_value_types vt)
+                       const json_path_t *p, json_value_types vt,
+                       const int *array_sizes)
 {
   for (; n_paths > 0; n_paths--, paths_list++)
   {
-    if (json_path_compare(&paths_list->p, p, vt) == 0)
+    if (json_path_compare(&paths_list->p, p, vt, array_sizes) == 0)
       return TRUE;
   }
   return FALSE;
@@ -808,17 +809,35 @@ static bool path_exact(const json_path_with_flags *paths_list, int n_paths,
 
 
 static bool path_ok(const json_path_with_flags *paths_list, int n_paths,
-                    const json_path_t *p, json_value_types vt)
+                    const json_path_t *p, json_value_types vt,
+                    const int *array_sizes)
 {
   for (; n_paths > 0; n_paths--, paths_list++)
   {
-    if (json_path_compare(&paths_list->p, p, vt) >= 0)
+    if (json_path_compare(&paths_list->p, p, vt, array_sizes) >= 0)
       return TRUE;
   }
   return FALSE;
 }
 
 
+static bool calculate_array_size(json_engine_t *je, int *array_size)
+{
+  /*
+    do the separate parsing to the end of the array and remember it's size
+    so we copy the engine to start from the same point, and the main engine
+    stays intact.
+  */
+  json_engine_t je2= *je;
+  if (json_skip_level_and_count(&je2, array_size))
+  {
+    *je= je2;
+    return TRUE;
+  }
+  return FALSE;
+}
+
+
 String *Item_func_json_extract::read_json(String *str,
                                           json_value_types *type,
                                           char **out_val, int *value_len)
@@ -831,6 +850,8 @@ String *Item_func_json_extract::read_json(String *str,
   uint n_arg;
   size_t v_len;
   int possible_multiple_values;
+  int array_sizes[JSON_DEPTH_LIMIT];
+  uint negative_path_used= 0;
 
   if ((null_value= args[0]->null_value))
     return 0;
@@ -849,6 +870,7 @@ String *Item_func_json_extract::read_json(String *str,
         goto return_null;
       }
       c_path->parsed= c_path->constant;
+      negative_path_used|= c_path->p.types_used & JSON_PATH_NEGATIVE_INDEX; 
     }
 
     if (args[n_arg]->null_value)
@@ -874,7 +896,15 @@ String *Item_func_json_extract::read_json(String *str,
 
   while (json_get_path_next(&je, &p) == 0)
   {
-    if (!path_exact(paths, arg_count-1, &p, je.value_type))
+    /*
+      To handle negative indexes we should calculate the array size
+      in advance.
+    */
+    if (negative_path_used && je.value_type == JSON_VALUE_ARRAY && 
+        calculate_array_size(&je, array_sizes + (p.last_step - p.steps)))
+      goto error;
+
+    if (!path_exact(paths, arg_count-1, &p, je.value_type, array_sizes))
       continue;
 
     value= je.value_begin;
@@ -1246,7 +1276,7 @@ longlong Item_func_json_contains::val_int()
 
   if (arg_count>2) /* Path specified. */
   {
-    uint array_counters[JSON_DEPTH_LIMIT];
+    int array_counters[JSON_DEPTH_LIMIT];
     if (!path.parsed)
     {
       String *s_p= args[2]->val_str(&tmp_path);
@@ -1376,7 +1406,7 @@ longlong Item_func_json_contains_path::val_int()
   result= !mode_one;
   for (n_arg=2; n_arg < arg_count; n_arg++)
   {
-    uint array_counters[JSON_DEPTH_LIMIT];
+    int array_counters[JSON_DEPTH_LIMIT];
     json_path_with_flags *c_path= paths + n_arg - 2;
     if (!c_path->parsed)
     {
@@ -1437,6 +1467,8 @@ longlong Item_func_json_contains_path::val_int()
   json_path_t p;
   int n_found;
   LINT_INIT(n_found);
+  int array_sizes[JSON_DEPTH_LIMIT];
+  uint negative_path_used= 0;
 
   if ((null_value= args[0]->null_value))
     return 0;
@@ -1458,6 +1490,7 @@ longlong Item_func_json_contains_path::val_int()
         goto null_return;
       }
       c_path->parsed= c_path->constant;
+      negative_path_used|= c_path->p.types_used & JSON_PATH_NEGATIVE_INDEX; 
     }
     if (args[n_arg]->null_value)
       goto null_return;
@@ -1479,10 +1512,20 @@ longlong Item_func_json_contains_path::val_int()
   while (json_get_path_next(&je, &p) == 0)
   {
     int n_path= arg_count - 2;
+    /*
+      To handle negative indexes we should calculate the array size
+      in advance.
+    */
+    if (negative_path_used && je.value_type == JSON_VALUE_ARRAY && 
+        calculate_array_size(&je, array_sizes + (p.last_step - p.steps)))
+    {
+      result= 1;
+      break;
+    }
     json_path_with_flags *c_path= paths;
     for (; n_path > 0; n_path--, c_path++)
     {
-      if (json_path_compare(&c_path->p, &p, je.value_type) >= 0)
+      if (json_path_compare(&c_path->p, &p, je.value_type, array_sizes) >= 0)
       {
         if (mode_one)
         {
@@ -1722,7 +1765,7 @@ String *Item_func_json_array_append::val_str(String *str)
 
   for (n_arg=1, n_path=0; n_arg < arg_count; n_arg+=2, n_path++)
   {
-    uint array_counters[JSON_DEPTH_LIMIT];
+    int array_counters[JSON_DEPTH_LIMIT];
     json_path_with_flags *c_path= paths + n_path;
     if (!c_path->parsed)
     {
@@ -1850,10 +1893,10 @@ String *Item_func_json_array_insert::val_str(String *str)
 
   for (n_arg=1, n_path=0; n_arg < arg_count; n_arg+=2, n_path++)
   {
-    uint array_counters[JSON_DEPTH_LIMIT];
+    int array_counters[JSON_DEPTH_LIMIT];
     json_path_with_flags *c_path= paths + n_path;
     const char *item_pos;
-    uint n_item;
+    int n_item, path_n_item;
 
     if (!c_path->parsed)
     {
@@ -1902,11 +1945,19 @@ String *Item_func_json_array_insert::val_str(String *str)
 
     item_pos= 0;
     n_item= 0;
+    path_n_item= c_path->p.last_step[1].n_item;
+    if (path_n_item < 0)
+    {
+      int array_size;
+      if (calculate_array_size(&je, &array_size))
+        goto js_error;
+      path_n_item+= array_size + 1;
+    }
 
     while (json_scan_next(&je) == 0 && je.state != JST_ARRAY_END)
     {
       DBUG_ASSERT(je.state == JST_VALUE);
-      if (n_item == c_path->p.last_step[1].n_item)
+      if (n_item == path_n_item)
       {
         item_pos= (const char *) je.s.c_str;
         break;
@@ -2632,7 +2683,7 @@ longlong Item_func_json_length::val_int()
   String *js= args[0]->val_json(&tmp_js);
   json_engine_t je;
   uint length= 0;
-  uint array_counters[JSON_DEPTH_LIMIT];
+  int array_counters[JSON_DEPTH_LIMIT];
   int err;
 
   if ((null_value= args[0]->null_value))
@@ -2859,10 +2910,11 @@ String *Item_func_json_insert::val_str(String *str)
 
   for (n_arg=1, n_path=0; n_arg < arg_count; n_arg+=2, n_path++)
   {
-    uint array_counters[JSON_DEPTH_LIMIT];
+    int array_counters[JSON_DEPTH_LIMIT];
     json_path_with_flags *c_path= paths + n_path;
     const char *v_to;
     const json_path_step_t *lp;
+    int path_n_item;
 
     if (!c_path->parsed)
     {
@@ -2907,7 +2959,7 @@ String *Item_func_json_insert::val_str(String *str)
     lp= c_path->p.last_step+1;
     if (lp->type & JSON_PATH_ARRAY)
     {
-      uint n_item= 0;
+      int n_item= 0;
 
       if (je.value_type != JSON_VALUE_ARRAY)
       {
@@ -2956,12 +3008,21 @@ String *Item_func_json_insert::val_str(String *str)
         goto continue_point;
       }
 
+      path_n_item= lp->n_item;
+      if (path_n_item < 0)
+      {
+        int array_size;
+        if (calculate_array_size(&je, &array_size))
+          goto js_error;
+        path_n_item+= array_size;
+      }
+
       while (json_scan_next(&je) == 0 && je.state != JST_ARRAY_END)
       {
         switch (je.state)
         {
         case JST_VALUE:
-          if (n_item == lp->n_item)
+          if (n_item == path_n_item)
             goto v_found;
           n_item++;
           if (json_skip_array_item(&je))
@@ -3110,11 +3171,11 @@ String *Item_func_json_remove::val_str(String *str)
 
   for (n_arg=1, n_path=0; n_arg < arg_count; n_arg++, n_path++)
   {
-    uint array_counters[JSON_DEPTH_LIMIT];
+    int array_counters[JSON_DEPTH_LIMIT];
     json_path_with_flags *c_path= paths + n_path;
     const char *rem_start= 0, *rem_end;
     const json_path_step_t *lp;
-    uint n_item= 0;
+    int n_item= 0;
 
     if (!c_path->parsed)
     {
@@ -3160,15 +3221,26 @@ String *Item_func_json_remove::val_str(String *str)
     lp= c_path->p.last_step+1;
     if (lp->type & JSON_PATH_ARRAY)
     {
+      int path_n_item;
+
       if (je.value_type != JSON_VALUE_ARRAY)
         continue;
 
+      path_n_item= lp->n_item;
+      if (path_n_item < 0)
+      {
+        int array_size;
+        if (calculate_array_size(&je, &array_size))
+          goto js_error;
+        path_n_item+= array_size;
+      }
+
       while (json_scan_next(&je) == 0 && je.state != JST_ARRAY_END)
       {
         switch (je.state)
         {
         case JST_VALUE:
-          if (n_item == lp->n_item)
+          if (n_item == path_n_item)
           {
             rem_start= (const char *) (je.s.c_str -
                          (n_item ? je.sav_c_len : 0));
@@ -3320,7 +3392,7 @@ String *Item_func_json_keys::val_str(String *str)
   json_engine_t je;
   String *js= args[0]->val_json(&tmp_js);
   uint n_keys= 0;
-  uint array_counters[JSON_DEPTH_LIMIT];
+  int array_counters[JSON_DEPTH_LIMIT];
 
   if ((args[0]->null_value))
     goto null_return;
@@ -3527,6 +3599,8 @@ String *Item_func_json_search::val_str(String *str)
   json_engine_t je;
   json_path_t p, sav_path;
   uint n_arg;
+  int array_sizes[JSON_DEPTH_LIMIT];
+  uint negative_path_used= 0;
 
   if (args[0]->null_value || args[2]->null_value)
     goto null_return;
@@ -3552,6 +3626,7 @@ String *Item_func_json_search::val_str(String *str)
         goto null_return;
       }
       c_path->parsed= c_path->constant;
+      negative_path_used|= c_path->p.types_used & JSON_PATH_NEGATIVE_INDEX; 
     }
     if (args[n_arg]->null_value)
       goto null_return;
@@ -3562,9 +3637,18 @@ String *Item_func_json_search::val_str(String *str)
 
   while (json_get_path_next(&je, &p) == 0)
   {
+    /*
+      To handle negative indexes we should calculate the array size
+      in advance.
+    */
+    if (negative_path_used && je.value_type == JSON_VALUE_ARRAY && 
+        calculate_array_size(&je, array_sizes + (p.last_step - p.steps)))
+      goto js_error;
+
     if (json_value_scalar(&je))
     {
-      if ((arg_count < 5 || path_ok(paths, arg_count - 4, &p, je.value_type)) &&
+      if ((arg_count < 5 ||
+           path_ok(paths, arg_count - 4, &p, je.value_type, array_sizes)) &&
           compare_json_value_wild(&je, s_str) != 0)
       {
         ++n_path_found;
diff --git a/sql/json_table.cc b/sql/json_table.cc
index e57dccd00c4..30d59fb06b5 100644
--- a/sql/json_table.cc
+++ b/sql/json_table.cc
@@ -312,7 +312,8 @@ handle_new_nested:
 
   while (!json_get_path_next(&m_engine, &m_cur_path))
   {
-    if (json_path_compare(&m_path, &m_cur_path, m_engine.value_type))
+    if (json_path_compare(&m_path, &m_cur_path, m_engine.value_type,
+                          NULL))
       continue;
     /* path found. */
     ++m_ordinality_counter;
@@ -501,7 +502,7 @@ int ha_json_table::fill_column_values(THD *thd, uchar * buf, uchar *pos)
       {
         json_engine_t je;
         json_path_step_t *cur_step;
-        uint array_counters[JSON_DEPTH_LIMIT];
+        int array_counters[JSON_DEPTH_LIMIT];
         int not_found;
         const uchar* node_start;
         const uchar* node_end;
diff --git a/strings/json_lib.c b/strings/json_lib.c
index 7b895c216b5..9299bf7f58a 100644
--- a/strings/json_lib.c
+++ b/strings/json_lib.c
@@ -982,6 +982,7 @@ enum json_path_chr_classes {
   P_LSQRB,  /* [ */
   P_RSQRB,  /* ] */
   P_POINT,  /* . */
+  P_NEG,    /* - */
   P_ZERO,   /* 0 */
   P_DIGIT,  /* 123456789 */
   P_L,      /* l (for "lax") */
@@ -1003,7 +1004,7 @@ static enum json_path_chr_classes json_path_chr_map[128] = {
   P_ERR,   P_ERR,   P_ERR,   P_ERR,   P_ERR,   P_ERR,   P_ERR,   P_ERR,
 
   P_SPACE, P_ETC,   P_QUOTE, P_ETC,   P_USD,   P_ETC,   P_ETC,   P_ETC,
-  P_ETC,   P_ETC,   P_ASTER, P_ETC,   P_ETC,   P_ETC,   P_POINT, P_ETC,
+  P_ETC,   P_ETC,   P_ASTER, P_ETC,   P_ETC,   P_NEG,   P_POINT, P_ETC,
   P_ZERO,  P_DIGIT, P_DIGIT, P_DIGIT, P_DIGIT, P_DIGIT, P_DIGIT, P_DIGIT,
   P_DIGIT, P_DIGIT, P_ETC,   P_ETC,   P_ETC,   P_ETC,   P_ETC,   P_ETC,
 
@@ -1026,6 +1027,7 @@ enum json_path_states {
   PS_AR,  /* Parse array step. */
   PS_SAR, /* space after the '['. */
   PS_AWD, /* Array wildcard. */
+  PS_NEG, /* '-' (minus). */
   PS_Z,   /* '0' (as an array item number). */
   PS_INT, /* Parse integer (as an array item number). */
   PS_AS,  /* Space. */
@@ -1051,56 +1053,59 @@ enum json_path_states {
 static int json_path_transitions[N_PATH_STATES][N_PATH_CLASSES]=
 {
 /*
-            EOS       $,      *       [       ]       .       0
+            EOS       $,      *       [       ]       .       -     0
             1..9    L       S       SPACE   \       "       ETC
             ERR              BAD
 */
-/* GO  */ { JE_EOS, PS_PT,  JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
+/* GO  */ { JE_EOS, PS_PT,  JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
             JE_SYN, PS_LAX, PS_SCT, PS_GO,  JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* LAX */ { JE_EOS, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
+/* LAX */ { JE_EOS, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
             JE_SYN, PS_LAX, JE_SYN, PS_GO,  JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
 /* PT */  { PS_OK,  JE_SYN, PS_AST, PS_AR,  JE_SYN, PS_KEY, JE_SYN, JE_SYN,
-            JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
+            JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* AR */  { JE_EOS, JE_SYN, PS_AWD, JE_SYN, JE_SYN, JE_SYN, PS_Z,
+/* AR */  { JE_EOS, JE_SYN, PS_AWD, JE_SYN, JE_SYN, JE_SYN, PS_NEG, PS_Z,
             PS_INT, JE_SYN, JE_SYN, PS_SAR, JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* SAR */ { JE_EOS, JE_SYN, PS_AWD, JE_SYN, PS_PT,  JE_SYN, PS_Z,
+/* SAR */ { JE_EOS, JE_SYN, PS_AWD, JE_SYN, PS_PT,  JE_SYN, PS_NEG, PS_Z,
             PS_INT, JE_SYN, JE_SYN, PS_SAR, JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* AWD */ { JE_EOS, JE_SYN, JE_SYN, JE_SYN, PS_PT,  JE_SYN, JE_SYN,
+/* AWD */ { JE_EOS, JE_SYN, JE_SYN, JE_SYN, PS_PT,  JE_SYN, JE_SYN, JE_SYN,
             JE_SYN, JE_SYN, JE_SYN, PS_AS,  JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* Z */   { JE_EOS, JE_SYN, JE_SYN, JE_SYN, PS_PT,  JE_SYN, JE_SYN,
+/* NEG */ { JE_EOS, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
+            PS_INT, JE_SYN, JE_SYN, PS_NEG, JE_SYN, JE_SYN, JE_SYN,
+            JE_NOT_JSON_CHR, JE_BAD_CHR},
+/* Z */   { JE_EOS, JE_SYN, JE_SYN, JE_SYN, PS_PT,  JE_SYN, JE_SYN, JE_SYN,
             JE_SYN, JE_SYN, JE_SYN, PS_AS,  JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* INT */ { JE_EOS, JE_SYN, JE_SYN, JE_SYN, PS_PT,  JE_SYN, PS_INT,
+/* INT */ { JE_EOS, JE_SYN, JE_SYN, JE_SYN, PS_PT,  JE_SYN, JE_SYN, PS_INT,
             PS_INT, JE_SYN, JE_SYN, PS_AS,  JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
 /* AS */  { JE_EOS, JE_SYN, JE_SYN, JE_SYN, PS_PT,  JE_SYN, JE_SYN, JE_SYN,
-            JE_SYN, JE_SYN, PS_AS,  JE_SYN, JE_SYN, JE_SYN,
+            JE_SYN, JE_SYN, JE_SYN, PS_AS,  JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* KEY */ { JE_EOS, PS_KNM, PS_KWD, JE_SYN, PS_KNM, JE_SYN, PS_KNM,
+/* KEY */ { JE_EOS, PS_KNM, PS_KWD, JE_SYN, PS_KNM, JE_SYN, JE_SYN, PS_KNM,
             PS_KNM, PS_KNM, PS_KNM, PS_KNM, JE_SYN, PS_KEYX, PS_KNM,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* KNM */ { PS_KOK, PS_KNM, PS_AST, PS_EAR, PS_KNM, PS_EKY, PS_KNM,
+/* KNM */ { PS_KOK, PS_KNM, PS_AST, PS_EAR, PS_KNM, PS_EKY, JE_SYN, PS_KNM,
             PS_KNM, PS_KNM, PS_KNM, PS_KNM, PS_ESC, PS_KNM, PS_KNM,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* KWD */ { PS_OK,  JE_SYN, JE_SYN, PS_AR,  JE_SYN, PS_EKY, JE_SYN,
+/* KWD */ { PS_OK,  JE_SYN, JE_SYN, PS_AR,  JE_SYN, PS_EKY, JE_SYN, JE_SYN,
             JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* AST */ { JE_SYN, JE_SYN, PS_DWD, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
+/* AST */ { JE_SYN, JE_SYN, PS_DWD, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
             JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
 /* DWD */ { JE_SYN, JE_SYN, PS_AST, PS_AR,  JE_SYN, PS_KEY, JE_SYN, JE_SYN,
-            JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
+            JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN, JE_SYN,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* KEYX*/ { JE_EOS, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX,
+/* KEYX*/ { JE_EOS, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX, JE_SYN,PS_KNMX,
             PS_KNMX,PS_KNMX, PS_KNMX, PS_KNMX, PS_ESCX, PS_EKYX, PS_KNMX,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
-/* KNMX */{ JE_EOS, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX,
+/* KNMX */{ JE_EOS, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX, JE_SYN,PS_KNMX,
             PS_KNMX, PS_KNMX, PS_KNMX, PS_KNMX,PS_ESCX, PS_EKYX, PS_KNMX,
             JE_NOT_JSON_CHR, JE_BAD_CHR},
 };
@@ -1111,6 +1116,7 @@ int json_path_setup(json_path_t *p,
 {
   int c_len, t_next, state= PS_GO;
   enum json_path_step_types double_wildcard= JSON_PATH_KEY_NULL;
+  int neg_int;
 
   json_string_setup(&p->s, i_cs, str, end);
 
@@ -1149,9 +1155,16 @@ int json_path_setup(json_path_t *p,
       p->last_step->type|= JSON_PATH_WILD;
       p->types_used|= JSON_PATH_WILD;
       continue;
+    case PS_NEG:
+      neg_int= 1;
+      p->types_used|= JSON_PATH_NEGATIVE_INDEX;
+      continue;
     case PS_INT:
       p->last_step->n_item*= 10;
-      p->last_step->n_item+= p->s.c_next - '0';
+      if (neg_int)
+        p->last_step->n_item-= p->s.c_next - '0';
+      else
+        p->last_step->n_item+= p->s.c_next - '0';
       continue;
     case PS_EKYX:
       p->last_step->key_end= p->s.c_str - c_len;
@@ -1182,6 +1195,7 @@ int json_path_setup(json_path_t *p,
       p->types_used|= p->last_step->type= JSON_PATH_ARRAY | double_wildcard;
       double_wildcard= JSON_PATH_KEY_NULL;
       p->last_step->n_item= 0;
+      neg_int= 0;
       continue;
     case PS_ESC:
       if (json_handle_esc(&p->s))
@@ -1251,7 +1265,7 @@ int json_skip_key(json_engine_t *j)
 }
 
 
-#define SKIPPED_STEP_MARK ((uint) ~0)
+#define SKIPPED_STEP_MARK ((int) ~0)
 
 /*
   Current step of the patch matches the JSON construction.
@@ -1259,7 +1273,7 @@ int json_skip_key(json_engine_t *j)
   step of the path.
 */
 static int handle_match(json_engine_t *je, json_path_t *p,
-                        json_path_step_t **p_cur_step, uint *array_counters)
+                        json_path_step_t **p_cur_step, int *array_counters)
 {
   json_path_step_t *next_step= *p_cur_step + 1;
 
@@ -1297,12 +1311,28 @@ static int handle_match(json_engine_t *je, json_path_t *p,
   }
 
 
-  array_counters[next_step - p->steps]= 0;
-
   if ((int) je->value_type !=
       (int) (next_step->type & JSON_PATH_KEY_OR_ARRAY))
     return json_skip_level(je);
 
+  if (next_step->type == JSON_PATH_ARRAY)
+  {
+    int array_size;
+    if (next_step->n_item >= 0)
+      array_size= 0;
+    else
+    {
+      json_engine_t j2= *je;
+      if (json_skip_level_and_count(&j2, &array_size))
+      {
+        *je= j2;
+        return 1;
+      }
+      array_size= -array_size;
+    }
+    array_counters[next_step - p->steps]= array_size;
+  }
+
   *p_cur_step= next_step;
   return 0;
 }
@@ -1327,7 +1357,7 @@ int json_key_matches(json_engine_t *je, json_string_t *k)
 
 int json_find_path(json_engine_t *je,
                    json_path_t *p, json_path_step_t **p_cur_step,
-                   uint *array_counters)
+                   int *array_counters)
 {
   json_string_t key_name;
 
@@ -1770,9 +1800,10 @@ int json_get_path_next(json_engine_t *je, json_path_t *p)
 int json_path_parts_compare(
     const json_path_step_t *a, const json_path_step_t *a_end,
     const json_path_step_t *b, const json_path_step_t *b_end,
-    enum json_value_types vt)
+    enum json_value_types vt, const int *array_sizes)
 {
   int res, res2;
+  const json_path_step_t *orig_b= b;
 
   while (a <= a_end)
   {
@@ -1795,7 +1826,9 @@ int json_path_parts_compare(
     {
       if (b->type & JSON_PATH_ARRAY)
       {
-        if ((a->type & JSON_PATH_WILD) || a->n_item == b->n_item)
+        if ((a->type & JSON_PATH_WILD) ||
+            (a->n_item >= 0 ? a->n_item == b->n_item :
+                              a->n_item == b->n_item - array_sizes[b-orig_b]))
           goto step_fits;
         goto step_failed;
       }
@@ -1830,11 +1863,13 @@ step_fits:
     }
 
     /* Double wild handling needs recursions. */
-    res= json_path_parts_compare(a+1, a_end, b, b_end, vt);
+    res= json_path_parts_compare(a+1, a_end, b, b_end, vt,
+                                 array_sizes + (b - orig_b));
     if (res == 0)
       return 0;
 
-    res2= json_path_parts_compare(a, a_end, b, b_end, vt);
+    res2= json_path_parts_compare(a, a_end, b, b_end, vt,
+                                  array_sizes + (b - orig_b));
 
     return (res2 >= 0) ? res2 : res;
 
@@ -1846,11 +1881,13 @@ step_fits_autowrap:
     }
 
     /* Double wild handling needs recursions. */
-    res= json_path_parts_compare(a+1, a_end, b+1, b_end, vt);
+    res= json_path_parts_compare(a+1, a_end, b+1, b_end, vt,
+                                 array_sizes + (b - orig_b));
     if (res == 0)
       return 0;
 
-    res2= json_path_parts_compare(a, a_end, b+1, b_end, vt);
+    res2= json_path_parts_compare(a, a_end, b+1, b_end, vt,
+                                  array_sizes + (b - orig_b));
 
     return (res2 >= 0) ? res2 : res;
 
@@ -1861,10 +1898,10 @@ step_fits_autowrap:
 
 
 int json_path_compare(const json_path_t *a, const json_path_t *b,
-                      enum json_value_types vt)
+                      enum json_value_types vt, const int *array_sizes)
 {
   return json_path_parts_compare(a->steps+1, a->last_step,
-                                 b->steps+1, b->last_step, vt);
+                                 b->steps+1, b->last_step, vt, array_sizes);
 }
 
 
diff --git a/unittest/json_lib/json_lib-t.c b/unittest/json_lib/json_lib-t.c
index 378ebe201f5..c5a6df54e1d 100644
--- a/unittest/json_lib/json_lib-t.c
+++ b/unittest/json_lib/json_lib-t.c
@@ -140,7 +140,7 @@ test_search()
   json_path_t p;
   json_path_step_t *cur_step;
   int n_matches, scal_values;
-  uint array_counters[JSON_DEPTH_LIMIT];
+  int array_counters[JSON_DEPTH_LIMIT];
 
   if (json_scan_start(&je, ci, s_e(fj0)) ||
       json_path_setup(&p, ci, s_e(fp0)))