MDEV-27677: Implement JSON_OVERLAPS()

1) When at least one of the two json documents is of scalar type:
     1.a) If value and json document both are scalar, then return true
          if they have same type and value.
     1.b) If json document is scalar but other is array (or vice versa),
          then return true if array has at least one element of same type
          and value as scalar.
     1.c) If one is scalar and other is object, then return false because
          it can't be compared.

  2) When both arguments are of non-scalar type and below conditons
      are satisfied then return true:
      2.a) When both arguments are arrays:
           Iterate over the value and json document. If there exists at
           least one element in other array of same type and value as
           that of element in value.
      2.b) If both arguments are objects:
           Iterate over value and json document and if there exists at least
           one key-value pair common between two objects.
      2.c) If either of json document or value is array and other is object:
           Iterate over the array, if an element of type object is found,
           then compare it with the object (which is the other arguemnt).
           If the entire object matches i.e all they key value pairs match.

5 files changed, 914 insertions, 0 deletions

diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result
index f9845357d51..54cabeaa2c3 100644
--- a/mysql-test/main/func_json.result
+++ b/mysql-test/main/func_json.result
@@ -1428,3 +1428,323 @@ DROP TABLE t;
 #
 # End of 10.5 tests
 #
+#
+# Beginning of 10.9 tests
+#
+# MDEV-27677: Implement JSON_OVERLAPS()
+#
+# Testing scalar json datatypes
+# Comparing scalar json datatypes with itself
+SELECT JSON_OVERLAPS('true', 'true');
+JSON_OVERLAPS('true', 'true')
+1
+SELECT JSON_OVERLAPS('false', 'false');
+JSON_OVERLAPS('false', 'false')
+1
+SELECT JSON_OVERLAPS('1', '1');
+JSON_OVERLAPS('1', '1')
+1
+SELECT JSON_OVERLAPS('"string1"', '"string1"');
+JSON_OVERLAPS('"string1"', '"string1"')
+1
+SELECT JSON_OVERLAPS('null', 'null');
+JSON_OVERLAPS('null', 'null')
+1
+# Comparing scalar json datatypes with other scalar datatype
+SELECT JSON_OVERLAPS('true', 'false');
+JSON_OVERLAPS('true', 'false')
+0
+SELECT JSON_OVERLAPS('1', '"1"');
+JSON_OVERLAPS('1', '"1"')
+0
+SELECT JSON_OVERLAPS('1', '0');
+JSON_OVERLAPS('1', '0')
+0
+SELECT JSON_OVERLAPS('null', '0');
+JSON_OVERLAPS('null', '0')
+0
+SELECT JSON_OVERLAPS('"string1"', '"string2"');
+JSON_OVERLAPS('"string1"', '"string2"')
+0
+SELECT JSON_OVERLAPS('true','["abc", 1, 2, true, false]');
+JSON_OVERLAPS('true','["abc", 1, 2, true, false]')
+1
+SELECT JSON_OVERLAPS('true','["abc", 1, 2, [true]]');
+JSON_OVERLAPS('true','["abc", 1, 2, [true]]')
+0
+SELECT JSON_OVERLAPS('true','{"A":true}');
+JSON_OVERLAPS('true','{"A":true}')
+0
+# Testing non-scalar json data types
+# Comparing object with object (non-nested)
+SELECT JSON_OVERLAPS('{"A":[1, 2, 3]}','{}');
+JSON_OVERLAPS('{"A":[1, 2, 3]}','{}')
+0
+SELECT JSON_OVERLAPS('{"A": 1}',
+'{"A": 1}');
+JSON_OVERLAPS('{"A": 1}',
+'{"A": 1}')
+1
+SELECT JSON_OVERLAPS('{"A": 1}',
+'{"B": 1}');
+JSON_OVERLAPS('{"A": 1}',
+'{"B": 1}')
+0
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+'{
+                        "A": 2,
+                        "B": "string1"
+                      }');
+JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+'{
+                        "A": 2,
+                        "B": "string1"
+                      }')
+1
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+'{
+                       "A": 2,
+                       "B": "string2"
+                      }');
+JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+'{
+                       "A": 2,
+                       "B": "string2"
+                      }')
+0
+# Comparing nested object with other nested object
+SELECT JSON_OVERLAPS('{
+                        "A": 1,
+                        "B": {"C":2}
+                      }',
+'{
+                        "A": 2,
+                        "B": {"C":1}
+                      }');
+JSON_OVERLAPS('{
+                        "A": 1,
+                        "B": {"C":2}
+                      }',
+'{
+                        "A": 2,
+                        "B": {"C":1}
+                      }')
+0
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": {"C":2}
+                      }',
+'{
+                        "A": 2,
+                        "B": {"C":2}
+                      }');
+JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": {"C":2}
+                      }',
+'{
+                        "A": 2,
+                        "B": {"C":2}
+                      }')
+1
+SELECT JSON_OVERLAPS('{
+                       "A": {
+                             "B": true
+                            }
+                      }',
+'{
+                       "A": {
+                             "B": true,
+                             "C": false
+                            }
+                      }');
+JSON_OVERLAPS('{
+                       "A": {
+                             "B": true
+                            }
+                      }',
+'{
+                       "A": {
+                             "B": true,
+                             "C": false
+
+0
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}',
+'{"C":3, "B":{"E":5, "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}',
+'{"C":3, "B":{"E":5, "D":4}}')
+1
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":5, "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":5, "D":4}}')
+0
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":[5, 6, 7], "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":[5, 6, 7], "D":4}}')
+1
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":[7, 6 ,5], "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "B":{"E":[7, 6 ,5], "D":4}}')
+0
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "F":{"E":[5, 6, 7], "D":4}}');
+JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+'{"C":3, "F":{"E":[5, 6, 7], "D":4}}')
+0
+# Comparing array with array (non-nested)
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, 1]');
+JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, 1]')
+1
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, 5]');
+JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, 5]')
+0
+SELECT JSON_OVERLAPS('[1,2,3]','[]');
+JSON_OVERLAPS('[1,2,3]','[]')
+0
+# Comparing nested arrays
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, [1]]');
+JSON_OVERLAPS('[1, 2, true, false, null]',
+'[3, 4, [1]]')
+0
+SELECT JSON_OVERLAPS('[1, 2, [true, false], null]',
+'[[1], [true, false]]');
+JSON_OVERLAPS('[1, 2, [true, false], null]',
+'[[1], [true, false]]')
+1
+SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]');
+JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]')
+0
+# Comparing one non-scalar json datatypes with another non-scalar
+# json datatype
+# Comparing array with object
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+'{"A": 1}');
+JSON_OVERLAPS('[1, 2, true, false, null]',
+'{"A": 1}')
+0
+SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]',
+'{"A": 1}');
+JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]',
+'{"A": 1}')
+0
+SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]',
+'{"A": 1}');
+JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]',
+'{"A": 1}')
+1
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+'{"A": 1, "B": 2}');
+JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+'{"A": 1, "B": 2}')
+1
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+'{"A": 1, "B": 3}');
+JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+'{"A": 1, "B": 3}')
+0
+# Comparing nested array with object
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1, "B": 2}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1, "B": 2}')
+0
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1, "B": 3}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1, "B": 3}')
+0
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+'{"A": 1}')
+0
+# Comparing array with nested object
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]',
+'{"A": 1, "B": {"C": 12}}');
+JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]',
+'{"A": 1, "B": {"C": 12}}')
+1
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B": {"C": 12}}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B": {"C": 12}}')
+0
+# Comparing nested array with nested objects
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B":{"C": 12}}');
+JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B":{"C": 12}}')
+0
+SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B": {"C": 12}}');
+JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]',
+'{"A": 1, "B": {"C": 12}}')
+0
+# Comparing object with array
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 2}]');
+JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 2}]')
+0
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 3}]');
+JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 3}]')
+1
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]');
+JSON_OVERLAPS('{"A": 1, "B": 3}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]')
+1
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]');
+JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]')
+1
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A":1, "B":[1, 2, {"C": 3, "D": 5}]}]');
+JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+'[1, 2, true, false, {"A": 1, "B": 2}, {"A":1, "B":[1, 2, {"C": 3, "D": 5}]}]')
+1
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+'[1, 2, true, false, {"A": 1, "B": 2},{"A": 1, "B": [1, 2, {"C": 3, "D": 4}]}]');
+JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+'[1, 2, true, false, {"A": 1, "B": 2},{"A": 1, "B": [1, 2, {"C": 3, "D": 4}]}]')
+0
+# Comparing object with nested array
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]');
+JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]')
+0
+# Checking errors and warnings
+SELECT JSON_OVERLAPS('[1,2,{"A":B}]', '{"A":B}', '{"C":"string1"}');
+ERROR 42000: Incorrect parameter count in the call to native function 'JSON_OVERLAPS'
+SELECT JSON_OVERLAPS('[1,2,{"A":B}]');
+ERROR 42000: Incorrect parameter count in the call to native function 'JSON_OVERLAPS'
+SELECT JSON_OVERLAPS('','');
+JSON_OVERLAPS('','')
+1
+Warnings:
+Warning	4037	Unexpected end of JSON text in argument 1 to function 'json_overlaps'
+#
+# End of 10.9 test
+#
diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test
index 939f19b0b15..b7c8826550d 100644
--- a/mysql-test/main/func_json.test
+++ b/mysql-test/main/func_json.test
@@ -917,3 +917,174 @@ DROP TABLE t;
 --echo # End of 10.5 tests
 --echo #
 
+--echo #
+--echo # Beginning of 10.9 tests
+--echo #
+--echo # MDEV-27677: Implement JSON_OVERLAPS()
+--echo #
+
+
+--echo # Testing scalar json datatypes
+
+--echo # Comparing scalar json datatypes with itself
+SELECT JSON_OVERLAPS('true', 'true');
+SELECT JSON_OVERLAPS('false', 'false');
+SELECT JSON_OVERLAPS('1', '1');
+SELECT JSON_OVERLAPS('"string1"', '"string1"');
+SELECT JSON_OVERLAPS('null', 'null');
+
+--echo # Comparing scalar json datatypes with other scalar datatype
+SELECT JSON_OVERLAPS('true', 'false');
+SELECT JSON_OVERLAPS('1', '"1"');
+SELECT JSON_OVERLAPS('1', '0');
+SELECT JSON_OVERLAPS('null', '0');
+SELECT JSON_OVERLAPS('"string1"', '"string2"');
+
+SELECT JSON_OVERLAPS('true','["abc", 1, 2, true, false]');
+SELECT JSON_OVERLAPS('true','["abc", 1, 2, [true]]');
+SELECT JSON_OVERLAPS('true','{"A":true}');
+
+
+--echo # Testing non-scalar json data types
+
+--echo # Comparing object with object (non-nested)
+SELECT JSON_OVERLAPS('{"A":[1, 2, 3]}','{}');
+SELECT JSON_OVERLAPS('{"A": 1}',
+                     '{"A": 1}');
+SELECT JSON_OVERLAPS('{"A": 1}',
+                     '{"B": 1}');
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+                     '{
+                        "A": 2,
+                        "B": "string1"
+                      }');
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": "string1"
+                      }',
+                     '{
+                       "A": 2,
+                       "B": "string2"
+                      }');
+
+--echo # Comparing nested object with other nested object
+SELECT JSON_OVERLAPS('{
+                        "A": 1,
+                        "B": {"C":2}
+                      }',
+                     '{
+                        "A": 2,
+                        "B": {"C":1}
+                      }');
+SELECT JSON_OVERLAPS('{
+                       "A": 1,
+                       "B": {"C":2}
+                      }',
+                     '{
+                        "A": 2,
+                        "B": {"C":2}
+                      }');
+SELECT JSON_OVERLAPS('{
+                       "A": {
+                             "B": true
+                            }
+                      }',
+                     '{
+                       "A": {
+                             "B": true,
+                             "C": false
+                            }
+                      }');
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}',
+                     '{"C":3, "B":{"E":5, "D":4}}');
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+                     '{"C":3, "B":{"E":5, "D":4}}');
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+                     '{"C":3, "B":{"E":[5, 6, 7], "D":4}}');
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+                     '{"C":3, "B":{"E":[7, 6 ,5], "D":4}}');
+SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}',
+                     '{"C":3, "F":{"E":[5, 6, 7], "D":4}}');
+
+
+--echo # Comparing array with array (non-nested)
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+                     '[3, 4, 1]');
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+                     '[3, 4, 5]');
+SELECT JSON_OVERLAPS('[1,2,3]','[]');
+
+--echo # Comparing nested arrays
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+                     '[3, 4, [1]]');
+SELECT JSON_OVERLAPS('[1, 2, [true, false], null]',
+                     '[[1], [true, false]]');
+SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]');
+
+
+--echo # Comparing one non-scalar json datatypes with another non-scalar
+--echo # json datatype
+
+--echo # Comparing array with object
+SELECT JSON_OVERLAPS('[1, 2, true, false, null]',
+                     '{"A": 1}');
+SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]',
+                     '{"A": 1}');
+SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]',
+                     '{"A": 1}');
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+                     '{"A": 1, "B": 2}');
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]',
+                     '{"A": 1, "B": 3}');
+
+-- echo # Comparing nested array with object
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+                     '{"A": 1, "B": 2}');
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+                     '{"A": 1, "B": 3}');
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]',
+                     '{"A": 1}');
+
+--echo # Comparing array with nested object
+SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]',
+                     '{"A": 1, "B": {"C": 12}}');
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+                      '{"A": 1, "B": {"C": 12}}');
+
+--echo # Comparing nested array with nested objects
+SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]',
+                     '{"A": 1, "B":{"C": 12}}');
+SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]',
+                     '{"A": 1, "B": {"C": 12}}');
+
+--echo # Comparing object with array
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+                     '[1, 2, true, false, {"A": 1, "B": 2}]');
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+                     '[1, 2, true, false, {"A": 1, "B": 3}]');
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}',
+                     '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]');
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}',
+                     '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]');
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+                     '[1, 2, true, false, {"A": 1, "B": 2}, {"A":1, "B":[1, 2, {"C": 3, "D": 5}]}]');
+SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}',
+                     '[1, 2, true, false, {"A": 1, "B": 2},{"A": 1, "B": [1, 2, {"C": 3, "D": 4}]}]');
+
+--echo # Comparing object with nested array
+SELECT JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]');
+
+--echo # Checking errors and warnings
+
+--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
+SELECT JSON_OVERLAPS('[1,2,{"A":B}]', '{"A":B}', '{"C":"string1"}');
+--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
+SELECT JSON_OVERLAPS('[1,2,{"A":B}]');
+SELECT JSON_OVERLAPS('','');
+
+--echo #
+--echo # End of 10.9 test
+--echo #
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 2d76b04ac5d..390c80c64ca 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -1292,6 +1292,19 @@ protected:
 };
 
 
+class Create_func_json_overlaps: public Create_func_arg2
+{
+public:
+  virtual Item *create_2_arg(THD *thd, Item *arg1, Item *arg2);
+
+  static Create_func_json_overlaps s_singleton;
+
+protected:
+  Create_func_json_overlaps() {}
+  virtual ~Create_func_json_overlaps() {}
+};
+
+
 class Create_func_last_day : public Create_func_arg1
 {
 public:
@@ -4249,6 +4262,16 @@ Create_func_json_search::create_native(THD *thd, LEX_CSTRING *name,
 }
 
 
+Create_func_json_overlaps Create_func_json_overlaps::s_singleton;
+
+Item*
+Create_func_json_overlaps::create_2_arg(THD *thd, Item *arg1, Item *arg2)
+{
+  status_var_increment(thd->status_var.feature_json);
+  return new (thd->mem_root) Item_func_json_overlaps(thd, arg1, arg2);
+}
+
+
 Create_func_last_insert_id Create_func_last_insert_id::s_singleton;
 
 Item*
@@ -5667,6 +5690,7 @@ Native_func_registry func_array[] =
   { { STRING_WITH_LEN("JSON_QUERY") }, BUILDER(Create_func_json_query)},
   { { STRING_WITH_LEN("JSON_QUOTE") }, BUILDER(Create_func_json_quote)},
   { { STRING_WITH_LEN("JSON_OBJECT") }, BUILDER(Create_func_json_object)},
+  { { STRING_WITH_LEN("JSON_OVERLAPS") }, BUILDER(Create_func_json_overlaps)},
   { { STRING_WITH_LEN("JSON_REMOVE") }, BUILDER(Create_func_json_remove)},
   { { STRING_WITH_LEN("JSON_REPLACE") }, BUILDER(Create_func_json_replace)},
   { { STRING_WITH_LEN("JSON_SET") }, BUILDER(Create_func_json_set)},
diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc
index 89ac59098ac..a0dfcaae6e3 100644
--- a/sql/item_jsonfunc.cc
+++ b/sql/item_jsonfunc.cc
@@ -4031,3 +4031,370 @@ bool Item_func_json_normalize::fix_length_and_dec()
   set_maybe_null();
   return FALSE;
 }
+
+
+/*
+  When the two values match or don't match we need to return true or false.
+  But we can have some more elements in the array left or some more keys
+  left in the object that we no longer want to compare. In this case,
+  we want to skip the current item.
+*/
+void json_skip_current_level(json_engine_t *js, json_engine_t *value)
+{
+  json_skip_level(js);
+  json_skip_level(value);
+}
+
+
+/* At least one of the two arguments is a scalar. */
+bool json_find_overlap_with_scalar(json_engine_t *js, json_engine_t *value)
+{
+  if (json_value_scalar(value))
+  {
+    if (js->value_type == value->value_type)
+    {
+      if (js->value_type == JSON_VALUE_NUMBER)
+      {
+        double d_j, d_v;
+        char *end;
+        int err;
+
+        d_j= js->s.cs->strntod((char *) js->value, js->value_len, &end, &err);
+        d_v= value->s.cs->strntod((char *) value->value, value->value_len,
+                                   &end, &err);
+
+        return (fabs(d_j - d_v) < 1e-12);
+      }
+      else if (js->value_type == JSON_VALUE_STRING)
+      {
+        return value->value_len == js->value_len &&
+               memcmp(value->value, js->value, value->value_len) == 0;
+      }
+    }
+    return value->value_type == js->value_type;
+  }
+  else if (value->value_type == JSON_VALUE_ARRAY)
+  {
+    while (json_scan_next(value) == 0 && value->state == JST_VALUE)
+    {
+      if (json_read_value(value))
+        return FALSE;
+      if (js->value_type == value->value_type)
+      {
+        int res1= json_find_overlap_with_scalar(js, value);
+        if (res1)
+          return TRUE;
+      }
+      if (!json_value_scalar(value))
+        json_skip_level(value);
+    }
+  }
+  return FALSE;
+}
+
+
+/*
+  Compare when one is object and other is array. This means we are looking
+  for the object in the array. Hence, when value type of an element of the
+  array is object, then compare the two objects entirely. If they are
+  equal return true else return false.
+*/
+bool json_compare_arr_and_obj(json_engine_t *js, json_engine_t *value)
+{
+  st_json_engine_t loc_val= *value;
+  while (json_scan_next(js) == 0 && js->state == JST_VALUE)
+  {
+    if (json_read_value(js))
+      return FALSE;
+    if (js->value_type == JSON_VALUE_OBJECT)
+    {
+      int res1= json_find_overlap_with_object(js, value, true);
+      if (res1)
+        return TRUE;
+      *value= loc_val;
+    }
+    if (!json_value_scalar(js))
+      json_skip_level(js);
+  }
+  return FALSE;
+}
+
+
+bool json_compare_arrays_in_order(json_engine_t *js, json_engine_t *value)
+{
+  bool res= false;
+  while (json_scan_next(js) == 0 && json_scan_next(value) == 0 &&
+         js->state == JST_VALUE && value->state == JST_VALUE)
+  {
+    if (json_read_value(js) || json_read_value(value))
+      return FALSE;
+    if (js->value_type != value->value_type)
+    {
+      json_skip_current_level(js, value);
+      return FALSE;
+    }
+    res= check_overlaps(js, value, true);
+    if (!res)
+    {
+      json_skip_current_level(js, value);
+      return FALSE;
+    }
+  }
+  res= (value->state == JST_ARRAY_END || value->state == JST_OBJ_END ?
+        TRUE : FALSE);
+  json_skip_current_level(js, value);
+  return res;
+}
+
+
+int json_find_overlap_with_array(json_engine_t *js, json_engine_t *value,
+                                 bool compare_whole)
+{
+  if (value->value_type == JSON_VALUE_ARRAY)
+  {
+    if (compare_whole)
+      return json_compare_arrays_in_order(js, value);
+
+    json_engine_t loc_value= *value, current_js= *js;
+
+    while (json_scan_next(js) == 0 && js->state == JST_VALUE)
+    {
+      if (json_read_value(js))
+        return FALSE;
+      current_js= *js;
+      while (json_scan_next(value) == 0 && value->state == JST_VALUE)
+      {
+        if (json_read_value(value))
+          return FALSE;
+        if (js->value_type == value->value_type)
+        {
+          int res1= check_overlaps(js, value, true);
+          if (res1)
+            return TRUE;
+        }
+        else
+        {
+          if (!json_value_scalar(value))
+            json_skip_level(value);
+        }
+        *js= current_js;
+      }
+      *value= loc_value;
+      if (!json_value_scalar(js))
+        json_skip_level(js);
+    }
+    return FALSE;
+  }
+  else if (value->value_type == JSON_VALUE_OBJECT)
+  {
+    if (compare_whole)
+    {
+      json_skip_current_level(js, value);
+      return FALSE;
+    }
+    return json_compare_arr_and_obj(js, value);
+  }
+  else
+    return json_find_overlap_with_scalar(value, js);
+}
+
+
+int json_find_overlap_with_object(json_engine_t *js, json_engine_t *value,
+                                  bool compare_whole)
+{
+  if (value->value_type == JSON_VALUE_OBJECT)
+  {
+    /* Find at least one common key-value pair */
+    json_string_t key_name;
+    bool found_key= false, found_value= false;
+    json_engine_t loc_js= *js;
+    const uchar *k_start, *k_end;
+
+    json_string_set_cs(&key_name, value->s.cs);
+
+    while (json_scan_next(value) == 0 && value->state == JST_KEY)
+    {
+      k_start= value->s.c_str;
+      do
+      {
+        k_end= value->s.c_str;
+      } while (json_read_keyname_chr(value) == 0);
+
+      if (unlikely(value->s.error))
+        return FALSE;
+
+      json_string_set_str(&key_name, k_start, k_end);
+      found_key= find_key_in_object(js, &key_name);
+      found_value= 0;
+
+      if (found_key)
+      {
+        if (json_read_value(js) || json_read_value(value))
+          return FALSE;
+
+        /*
+          The value of key-value pair can be an be anything. If it is an object
+          then we need to compare the whole value and if it is an array then
+          we need to compare the elements in that order. So set compare_whole
+          to true.
+        */
+        if (js->value_type == value->value_type)
+          found_value= check_overlaps(js, value, true);
+        if (found_value)
+        {
+          if (!compare_whole)
+            return TRUE;
+          *js= loc_js;
+        }
+        else
+        {
+          if (compare_whole)
+          {
+            json_skip_current_level(js, value);
+            return FALSE;
+          }
+          *js= loc_js;
+        }
+      }
+      else
+      {
+        if (compare_whole)
+        {
+          json_skip_current_level(js, value);
+          return FALSE;
+        }
+        json_skip_key(value);
+        *js= loc_js;
+      }
+    }
+    json_skip_current_level(js, value);
+    return compare_whole ? TRUE : FALSE;
+  }
+  else if (value->value_type == JSON_VALUE_ARRAY)
+  {
+    if (compare_whole)
+    {
+      json_skip_current_level(js, value);
+      return FALSE;
+    }
+    return json_compare_arr_and_obj(value, js);
+  }
+  return FALSE;
+}
+
+
+/*
+  Find if two json documents overlap
+
+  SYNOPSIS
+    check_overlaps()
+    js     - json document
+    value  - value
+    compare_whole - If true then find full overlap with the document in case of
+                    object and comparing in-order in case of array.
+                    Else find at least one match between two objects or array.
+
+  IMPLEMENTATION
+  We can compare two json datatypes if they are of same type to check if
+  they are equal. When comparing between a json document and json value,
+  there can be following cases:
+  1) When at least one of the two json documents is of scalar type:
+     1.a) If value and json document both are scalar, then return true
+          if they have same type and value.
+     1.b) If json document is scalar but other is array (or vice versa),
+          then return true if array has at least one element of same type
+          and value as scalar.
+     1.c) If one is scalar and other is object, then return false because
+          it can't be compared.
+
+  2) When both arguments are of non-scalar type:
+      2.a) If both arguments are arrays:
+           Iterate over the value and json document. If there exists at least
+           one element in other array of same type and value as that of
+           element in value, then return true else return false.
+      2.b) If both arguments are objects:
+           Iterate over value and json document and if there exists at least
+           one key-value pair common between two objects, then return true,
+           else return false.
+      2.c) If either of json document or value is array and other is object:
+           Iterate over the array, if an element of type object is found,
+           then compare it with the object (which is the other arguemnt).
+           If the entire object matches i.e all they key value pairs match,
+           then return true else return false.
+
+  When we are comparing an object which is nested in other object or nested
+  in an array, we need to compare all the key-value pairs, irrespective of
+  what order they are in as opposed to non-nested where we return true if
+  at least one match is found. However, if we have an array nested in another
+  array, then we compare two arrays in that order i.e we compare
+  i-th element of array 1 with i-th element of array 2.
+
+  RETURN
+    FALSE - If two json documents do not overlap
+    TRUE  - if two json documents overlap
+*/
+int check_overlaps(json_engine_t *js, json_engine_t *value, bool compare_whole)
+{
+  switch (js->value_type)
+  {
+  case JSON_VALUE_OBJECT:
+    return json_find_overlap_with_object(js, value, compare_whole);
+  case JSON_VALUE_ARRAY:
+    return json_find_overlap_with_array(js, value, compare_whole);
+  default:
+    return json_find_overlap_with_scalar(js, value);
+  }
+}
+
+longlong Item_func_json_overlaps::val_int()
+{
+  String *js= args[0]->val_json(&tmp_js);
+  json_engine_t je, ve;
+  int result;
+
+  if ((null_value= args[0]->null_value))
+    return 0;
+
+  if (!a2_parsed)
+  {
+    val= args[1]->val_json(&tmp_val);
+    a2_parsed= a2_constant;
+  }
+
+  if (val == 0)
+  {
+    null_value= 1;
+    return 0;
+  }
+
+  json_scan_start(&je, js->charset(), (const uchar *) js->ptr(),
+                  (const uchar *) js->ptr() + js->length());
+
+  json_scan_start(&ve, val->charset(), (const uchar *) val->ptr(),
+                  (const uchar *) val->end());
+
+  if (json_read_value(&je) || json_read_value(&ve))
+    goto error;
+
+  result= check_overlaps(&je, &ve, false);
+  if (unlikely(je.s.error || ve.s.error))
+    goto error;
+
+  return result;
+
+error:
+  if (je.s.error)
+    report_json_error(js, &je, 0);
+  if (ve.s.error)
+    report_json_error(val, &ve, 1);
+  return 1;
+}
+
+bool Item_func_json_overlaps::fix_length_and_dec()
+{
+  a2_constant= args[1]->const_item();
+  a2_parsed= FALSE;
+  set_maybe_null();
+
+  return Item_bool_func::fix_length_and_dec();
+}
diff --git a/sql/item_jsonfunc.h b/sql/item_jsonfunc.h
index 70993228241..db2fd3da78f 100644
--- a/sql/item_jsonfunc.h
+++ b/sql/item_jsonfunc.h
@@ -47,6 +47,19 @@ void report_path_error_ex(const char *ps, json_path_t *p,
 void report_json_error_ex(const char *js, json_engine_t *je,
                           const char *fname, int n_param,
                           Sql_condition::enum_warning_level lv);
+int check_overlaps(json_engine_t *js, json_engine_t *value, bool compare_whole);
+int json_find_overlap_with_object(json_engine_t *js,
+                                              json_engine_t *value,
+                                              bool compare_whole);
+void json_skip_current_level(json_engine_t *js, json_engine_t *value);
+bool json_find_overlap_with_scalar(json_engine_t *js, json_engine_t *value);
+bool json_compare_arrays_in_order_in_order(json_engine_t *js, json_engine_t *value);
+bool json_compare_arr_and_obj(json_engine_t *js, json_engine_t* value);
+int json_find_overlap_with_array(json_engine_t *js,
+                                             json_engine_t *value,
+                                             bool compare_whole);
+
+
 
 class Json_engine_scan: public json_engine_t
 {
@@ -759,4 +772,23 @@ public:
 
 extern bool is_json_type(const Item *item);
 
+class Item_func_json_overlaps: public Item_bool_func
+{
+  String tmp_js;
+  bool a2_constant, a2_parsed;
+  String tmp_val, *val;
+public:
+  Item_func_json_overlaps(THD *thd, Item *a, Item *b):
+    Item_bool_func(thd, a, b) {}
+  LEX_CSTRING func_name_cstring() const override
+  {
+    static LEX_CSTRING name= {STRING_WITH_LEN("json_overlaps") };
+    return name;
+  }
+  bool fix_length_and_dec() override;
+  longlong val_int() override;
+  Item *get_copy(THD *thd) override
+  { return get_item_copy<Item_func_json_overlaps>(thd, this); }
+};
+
 #endif /* ITEM_JSONFUNC_INCLUDED */