Fixed #12990, Refs #27694 -- Added JSONField model field.

Thanks to Adam Johnson, Carlton Gibson, Mariusz Felisiak, and Raphael
Michel for mentoring this Google Summer of Code 2019 project and
everyone else who helped with the patch.

Special thanks to Mads Jensen, Nick Pope, and Simon Charette for
extensive reviews.

Co-authored-by: Mariusz Felisiak <felisiak.mariusz@gmail.com>

3 files changed, 49 insertions, 3 deletions

diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py
index 26968475bf..31e8a55a43 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -5,6 +5,7 @@ import datetime
 import decimal
 import functools
 import hashlib
+import json
 import math
 import operator
 import re
@@ -101,6 +102,7 @@ class DatabaseWrapper(BaseDatabaseWrapper):
         'BigIntegerField': 'bigint',
         'IPAddressField': 'char(15)',
         'GenericIPAddressField': 'char(39)',
+        'JSONField': 'text',
         'NullBooleanField': 'bool',
         'OneToOneField': 'integer',
         'PositiveBigIntegerField': 'bigint unsigned',
@@ -115,6 +117,7 @@ class DatabaseWrapper(BaseDatabaseWrapper):
     }
     data_type_check_constraints = {
         'PositiveBigIntegerField': '"%(column)s" >= 0',
+        'JSONField': '(JSON_VALID("%(column)s") OR "%(column)s" IS NULL)',
         'PositiveIntegerField': '"%(column)s" >= 0',
         'PositiveSmallIntegerField': '"%(column)s" >= 0',
     }
@@ -233,6 +236,7 @@ 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)
@@ -598,3 +602,11 @@ 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 817b1067e3..1b6f99a58c 100644
--- a/django/db/backends/sqlite3/features.py
+++ b/django/db/backends/sqlite3/features.py
@@ -1,4 +1,9 @@
+import operator
+
+from django.db import transaction
 from django.db.backends.base.features import BaseDatabaseFeatures
+from django.db.utils import OperationalError
+from django.utils.functional import cached_property
 
 from .base import Database
 
@@ -45,3 +50,14 @@ 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
+
+    @cached_property
+    def supports_json_field(self):
+        try:
+            with self.connection.cursor() as cursor, transaction.atomic():
+                cursor.execute('SELECT JSON(\'{"a": "b"}\')')
+        except OperationalError:
+            return False
+        return True
+
+    can_introspect_json_field = property(operator.attrgetter('supports_json_field'))
diff --git a/django/db/backends/sqlite3/introspection.py b/django/db/backends/sqlite3/introspection.py
index a203c454df..992e925e10 100644
--- a/django/db/backends/sqlite3/introspection.py
+++ b/django/db/backends/sqlite3/introspection.py
@@ -9,7 +9,7 @@ from django.db.backends.base.introspection import (
 from django.db.models import Index
 from django.utils.regex_helper import _lazy_re_compile
 
-FieldInfo = namedtuple('FieldInfo', BaseFieldInfo._fields + ('pk',))
+FieldInfo = namedtuple('FieldInfo', BaseFieldInfo._fields + ('pk', 'has_json_constraint'))
 
 field_size_re = _lazy_re_compile(r'^\s*(?:var)?char\s*\(\s*(\d+)\s*\)\s*$')
 
@@ -63,6 +63,8 @@ class DatabaseIntrospection(BaseDatabaseIntrospection):
             # No support for BigAutoField or SmallAutoField as SQLite treats
             # all integer primary keys as signed 64-bit integers.
             return 'AutoField'
+        if description.has_json_constraint:
+            return 'JSONField'
         return field_type
 
     def get_table_list(self, cursor):
@@ -81,12 +83,28 @@ class DatabaseIntrospection(BaseDatabaseIntrospection):
         interface.
         """
         cursor.execute('PRAGMA table_info(%s)' % self.connection.ops.quote_name(table_name))
+        table_info = cursor.fetchall()
+        json_columns = set()
+        if self.connection.features.can_introspect_json_field:
+            for line in table_info:
+                column = line[1]
+                json_constraint_sql = '%%json_valid("%s")%%' % column
+                has_json_constraint = cursor.execute("""
+                    SELECT sql
+                    FROM sqlite_master
+                    WHERE
+                        type = 'table' AND
+                        name = %s AND
+                        sql LIKE %s
+                """, [table_name, json_constraint_sql]).fetchone()
+                if has_json_constraint:
+                    json_columns.add(column)
         return [
             FieldInfo(
                 name, data_type, None, get_field_size(data_type), None, None,
-                not notnull, default, pk == 1,
+                not notnull, default, pk == 1, name in json_columns
             )
-            for cid, name, data_type, notnull, default, pk in cursor.fetchall()
+            for cid, name, data_type, notnull, default, pk in table_info
         ]
 
     def get_sequences(self, cursor, table_name, table_fields=()):