[3.1.x] Fixed #31836 -- Dropped support for JSONField __contains and __contained_by lookups on SQLite.

The current implementation works only for basic examples without
supporting nested structures and doesn't follow "the general principle
that the contained object must match the containing object as to
structure and data contents, possibly after discarding some
non-matching array elements or object key/value pairs from the
containing object".

Backport of ba691933cee375195c9c50f333dd4b2a3abbb726 from master.

8 files changed, 57 insertions, 54 deletions

diff --git a/django/db/backends/base/features.py b/django/db/backends/base/features.py
index 35ce3ba299..5a27aa9f17 100644
--- a/django/db/backends/base/features.py
+++ b/django/db/backends/base/features.py
@@ -311,6 +311,9 @@ class BaseDatabaseFeatures:
     has_native_json_field = False
     # Does the backend use PostgreSQL-style JSON operators like '->'?
     has_json_operators = False
+    # Does the backend support __contains and __contained_by lookups for
+    # a JSONField?
+    supports_json_field_contains = True
 
     def __init__(self, connection):
         self.connection = connection
diff --git a/django/db/backends/oracle/features.py b/django/db/backends/oracle/features.py
index bae09559ce..43a39061c1 100644
--- a/django/db/backends/oracle/features.py
+++ b/django/db/backends/oracle/features.py
@@ -61,3 +61,4 @@ class DatabaseFeatures(BaseDatabaseFeatures):
     allows_multiple_constraints_on_same_fields = False
     supports_boolean_expr_in_select_clause = False
     supports_primitives_in_json_field = False
+    supports_json_field_contains = False
diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py
index 31e8a55a43..4572636106 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -5,7 +5,6 @@ import datetime
 import decimal
 import functools
 import hashlib
-import json
 import math
 import operator
 import re
@@ -236,7 +235,6 @@ class DatabaseWrapper(BaseDatabaseWrapper):
         create_deterministic_function('DEGREES', 1, none_guard(math.degrees))
         create_deterministic_function('EXP', 1, none_guard(math.exp))
         create_deterministic_function('FLOOR', 1, none_guard(math.floor))
-        create_deterministic_function('JSON_CONTAINS', 2, _sqlite_json_contains)
         create_deterministic_function('LN', 1, none_guard(math.log))
         create_deterministic_function('LOG', 2, none_guard(lambda x, y: math.log(y, x)))
         create_deterministic_function('LPAD', 3, _sqlite_lpad)
@@ -602,11 +600,3 @@ def _sqlite_lpad(text, length, fill_text):
 @none_guard
 def _sqlite_rpad(text, length, fill_text):
     return (text + fill_text * length)[:length]
-
-
-@none_guard
-def _sqlite_json_contains(haystack, needle):
-    target, candidate = json.loads(haystack), json.loads(needle)
-    if isinstance(target, dict) and isinstance(candidate, dict):
-        return target.items() >= candidate.items()
-    return target == candidate
diff --git a/django/db/backends/sqlite3/features.py b/django/db/backends/sqlite3/features.py
index 1b6f99a58c..597055a8ee 100644
--- a/django/db/backends/sqlite3/features.py
+++ b/django/db/backends/sqlite3/features.py
@@ -50,6 +50,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
     supports_aggregate_filter_clause = Database.sqlite_version_info >= (3, 30, 1)
     supports_order_by_nulls_modifier = Database.sqlite_version_info >= (3, 30, 0)
     order_by_nulls_first = True
+    supports_json_field_contains = False
 
     @cached_property
     def supports_json_field(self):
diff --git a/django/db/models/fields/json.py b/django/db/models/fields/json.py
index b82c6a82e2..7199d86e04 100644
--- a/django/db/models/fields/json.py
+++ b/django/db/models/fields/json.py
@@ -140,28 +140,30 @@ class DataContains(PostgresOperatorLookup):
     postgres_operator = '@>'
 
     def as_sql(self, compiler, connection):
+        if not connection.features.supports_json_field_contains:
+            raise NotSupportedError(
+                'contains lookup is not supported on this database backend.'
+            )
         lhs, lhs_params = self.process_lhs(compiler, connection)
         rhs, rhs_params = self.process_rhs(compiler, connection)
         params = tuple(lhs_params) + tuple(rhs_params)
         return 'JSON_CONTAINS(%s, %s)' % (lhs, rhs), params
 
-    def as_oracle(self, compiler, connection):
-        raise NotSupportedError('contains lookup is not supported on Oracle.')
-
 
 class ContainedBy(PostgresOperatorLookup):
     lookup_name = 'contained_by'
     postgres_operator = '<@'
 
     def as_sql(self, compiler, connection):
+        if not connection.features.supports_json_field_contains:
+            raise NotSupportedError(
+                'contained_by lookup is not supported on this database backend.'
+            )
         lhs, lhs_params = self.process_lhs(compiler, connection)
         rhs, rhs_params = self.process_rhs(compiler, connection)
         params = tuple(rhs_params) + tuple(lhs_params)
         return 'JSON_CONTAINS(%s, %s)' % (rhs, lhs), params
 
-    def as_oracle(self, compiler, connection):
-        raise NotSupportedError('contained_by lookup is not supported on Oracle.')
-
 
 class HasKeyLookup(PostgresOperatorLookup):
     logical_operator = None
diff --git a/docs/releases/3.1.txt b/docs/releases/3.1.txt
index cd74eb81cb..22e47c93ae 100644
--- a/docs/releases/3.1.txt
+++ b/docs/releases/3.1.txt
@@ -533,7 +533,10 @@ backends.
   ``DatabaseFeatures.supports_json_field`` to ``False``. If storing primitives
   is not supported, set ``DatabaseFeatures.supports_primitives_in_json_field``
   to ``False``. If there is a true datatype for JSON, set
-  ``DatabaseFeatures.has_native_json_field`` to ``True``.
+  ``DatabaseFeatures.has_native_json_field`` to ``True``. If
+  :lookup:`jsonfield.contains` and :lookup:`jsonfield.contained_by` are not
+  supported, set ``DatabaseFeatures.supports_json_field_contains`` to
+  ``False``.
 
 * Third party database backends must implement introspection for ``JSONField``
   or set ``can_introspect_json_field`` to ``False``.
diff --git a/docs/topics/db/queries.txt b/docs/topics/db/queries.txt
index 2528144d68..b3463cb4ce 100644
--- a/docs/topics/db/queries.txt
+++ b/docs/topics/db/queries.txt
@@ -960,9 +960,9 @@ contained in the top-level of the field. For example::
     >>> Dog.objects.filter(data__contains={'breed': 'collie'})
     <QuerySet [<Dog: Meg>]>
 
-.. admonition:: Oracle
+.. admonition:: Oracle and SQLite
 
-    ``contains`` is not supported on Oracle.
+    ``contains`` is not supported on Oracle and SQLite.
 
 .. fieldlookup:: jsonfield.contained_by
 
@@ -984,9 +984,9 @@ subset of those in the value passed. For example::
     >>> Dog.objects.filter(data__contained_by={'breed': 'collie'})
     <QuerySet [<Dog: Fred>]>
 
-.. admonition:: Oracle
+.. admonition:: Oracle and SQLite
 
-    ``contained_by`` is not supported on Oracle.
+    ``contained_by`` is not supported on Oracle and SQLite.
 
 .. fieldlookup:: jsonfield.has_key
 
diff --git a/tests/model_fields/test_jsonfield.py b/tests/model_fields/test_jsonfield.py
index 7b81f3f22c..a36b1fee21 100644
--- a/tests/model_fields/test_jsonfield.py
+++ b/tests/model_fields/test_jsonfield.py
@@ -1,6 +1,6 @@
 import operator
 import uuid
-from unittest import mock, skipIf, skipUnless
+from unittest import mock, skipIf
 
 from django import forms
 from django.core import serializers
@@ -426,17 +426,20 @@ class TestQuerying(TestCase):
             [self.objs[3], self.objs[4], self.objs[6]],
         )
 
-    @skipIf(
-        connection.vendor == 'oracle',
-        "Oracle doesn't support contains lookup.",
-    )
+    @skipUnlessDBFeature('supports_json_field_contains')
     def test_contains(self):
         tests = [
             ({}, self.objs[2:5] + self.objs[6:8]),
             ({'baz': {'a': 'b', 'c': 'd'}}, [self.objs[7]]),
+            ({'baz': {'a': 'b'}}, [self.objs[7]]),
+            ({'baz': {'c': 'd'}}, [self.objs[7]]),
             ({'k': True, 'l': False}, [self.objs[6]]),
             ({'d': ['e', {'f': 'g'}]}, [self.objs[4]]),
+            ({'d': ['e']}, [self.objs[4]]),
+            ({'d': [{'f': 'g'}]}, [self.objs[4]]),
             ([1, [2]], [self.objs[5]]),
+            ([1], [self.objs[5]]),
+            ([[2]], [self.objs[5]]),
             ({'n': [None]}, [self.objs[4]]),
             ({'j': None}, [self.objs[4]]),
         ]
@@ -445,38 +448,32 @@ class TestQuerying(TestCase):
                 qs = NullableJSONModel.objects.filter(value__contains=value)
                 self.assertSequenceEqual(qs, expected)
 
-    @skipUnless(
-        connection.vendor == 'oracle',
-        "Oracle doesn't support contains lookup.",
-    )
+    @skipIfDBFeature('supports_json_field_contains')
     def test_contains_unsupported(self):
-        msg = 'contains lookup is not supported on Oracle.'
+        msg = 'contains lookup is not supported on this database backend.'
         with self.assertRaisesMessage(NotSupportedError, msg):
             NullableJSONModel.objects.filter(
                 value__contains={'baz': {'a': 'b', 'c': 'd'}},
             ).get()
 
-    @skipUnlessDBFeature('supports_primitives_in_json_field')
+    @skipUnlessDBFeature(
+        'supports_primitives_in_json_field',
+        'supports_json_field_contains',
+    )
     def test_contains_primitives(self):
         for value in self.primitives:
             with self.subTest(value=value):
                 qs = NullableJSONModel.objects.filter(value__contains=value)
                 self.assertIs(qs.exists(), True)
 
-    @skipIf(
-        connection.vendor == 'oracle',
-        "Oracle doesn't support contained_by lookup.",
-    )
+    @skipUnlessDBFeature('supports_json_field_contains')
     def test_contained_by(self):
         qs = NullableJSONModel.objects.filter(value__contained_by={'a': 'b', 'c': 14, 'h': True})
         self.assertSequenceEqual(qs, self.objs[2:4])
 
-    @skipUnless(
-        connection.vendor == 'oracle',
-        "Oracle doesn't support contained_by lookup.",
-    )
+    @skipIfDBFeature('supports_json_field_contains')
     def test_contained_by_unsupported(self):
-        msg = 'contained_by lookup is not supported on Oracle.'
+        msg = 'contained_by lookup is not supported on this database backend.'
         with self.assertRaisesMessage(NotSupportedError, msg):
             NullableJSONModel.objects.filter(value__contained_by={'a': 'b'}).get()
 
@@ -664,19 +661,25 @@ class TestQuerying(TestCase):
             ('value__baz__has_any_keys', ['a', 'x']),
             ('value__has_key', KeyTextTransform('foo', 'value')),
         )
-        # contained_by and contains lookups are not supported on Oracle.
-        if connection.vendor != 'oracle':
-            tests += (
-                ('value__contains', KeyTransform('bax', 'value')),
-                ('value__baz__contained_by', {'a': 'b', 'c': 'd', 'e': 'f'}),
-                (
-                    'value__contained_by',
-                    KeyTransform('x', RawSQL(
-                        self.raw_sql,
-                        ['{"x": {"a": "b", "c": 1, "d": "e"}}'],
-                    )),
-                ),
-            )
+        for lookup, value in tests:
+            with self.subTest(lookup=lookup):
+                self.assertIs(NullableJSONModel.objects.filter(
+                    **{lookup: value},
+                ).exists(), True)
+
+    @skipUnlessDBFeature('supports_json_field_contains')
+    def test_contains_contained_by_with_key_transform(self):
+        tests = [
+            ('value__contains', KeyTransform('bax', 'value')),
+            ('value__baz__contained_by', {'a': 'b', 'c': 'd', 'e': 'f'}),
+            (
+                'value__contained_by',
+                KeyTransform('x', RawSQL(
+                    self.raw_sql,
+                    ['{"x": {"a": "b", "c": 1, "d": "e"}}'],
+                )),
+            ),
+        ]
         for lookup, value in tests:
             with self.subTest(lookup=lookup):
                 self.assertIs(NullableJSONModel.objects.filter(