JSONField with SQL Server

This article explains how Django's JSONField works with SQL Server through the mssql-django backend, including supported lookups and limitations.

Prerequisites

• SQL Server 2016 or later (JSON functions are required)
• mssql-django 1.2 or later

How JSONField maps to SQL Server

Django's JSONField maps to nvarchar(max) with a JSON check constraint in SQL Server. The backend uses SQL Server's built-in JSON functions (JSON_VALUE, JSON_QUERY, ISJSON) to implement lookups and queries.

Define a model with JSONField

Add the following model to myapp/models.py. The examples in this article use an Item model so they don't conflict with the Product model from the Django quickstart.

from django.db import models

class Item(models.Model):
    name = models.CharField(max_length=100)
    metadata = models.JSONField(default=dict)
    tags = models.JSONField(null=True, blank=True)

Generate and apply the migration so the underlying table exists in SQL Server:

python manage.py makemigrations myapp
python manage.py migrate myapp

Store and retrieve JSON data

Open the Django shell with python manage.py shell. At the >>> prompt, import the model:

from myapp.models import Item

Create a record with JSON data:

item = Item.objects.create(
    name="Widget",
    metadata={"color": "blue", "weight": 1.5, "dimensions": {"height": 10, "width": 5}},
    tags=["sale", "new"],
)

Retrieve the record and access JSON values:

item = Item.objects.get(name="Widget")
print(item.metadata["color"])  # "blue"
print(item.tags)  # ["sale", "new"]

Supported lookups

The mssql-django backend supports the following JSONField lookups:

Key/index lookups

Access nested JSON values using Django's double-underscore syntax:

# Filter by nested key value
Item.objects.filter(metadata__color="blue").values()

# Access nested objects
Item.objects.filter(metadata__dimensions__height=10).values()

contains

# Instead of: Item.objects.filter(metadata__contains={"color": "blue"})
# Use key-path lookup:
Item.objects.filter(metadata__color="blue").values()

has_key

Check if a specific key exists:

Item.objects.filter(metadata__has_key="color").values()

has_keys

Check if all specified keys exist:

Item.objects.filter(metadata__has_keys=["color", "weight"]).values()

has_any_keys

Check if any of the specified keys exist:

Item.objects.filter(metadata__has_any_keys=["color", "size"]).values()

isnull

The isnull lookup has specific behavior with SQL Server:

# Returns objects where the key doesn't exist AND keys with None value
Item.objects.filter(metadata__color__isnull=True).values()

# Returns objects where the key exists and has a non-null value
Item.objects.filter(metadata__color__isnull=False).values()

exact with None

The exact lookup doesn't support None values. The following query returns an empty QuerySet:

# Returns empty QuerySet - use isnull lookup instead
Item.objects.filter(metadata__color=None).values()

Use the isnull lookup instead to find null values.

Limitations

• Bulk updates with JSONField: Some edge cases exist when using bulk_update with JSONField values, particularly on Django 5.2 and later versions. For more information, see Limitations and unsupported features in mssql-django.
• CASE WHEN expressions: On Django 5.2 and later versions, certain JSONField operations inside CASE WHEN expressions might produce unexpected results.
• exact with None: Use isnull instead of exact to filter for null JSON values.
• has_key with null values: has_key returns an empty QuerySet for keys that exist but have a null value.
• Literal quote characters in JSON string values: Equality lookups on JSON string values that contain literal " characters (for example, metadata={"description": '"quoted"'}) might not match the stored row. Values containing quote characters store correctly but aren't reliably retrievable through field lookups.

Related content

• Migrate Django apps from PostgreSQL to SQL Server
• Django field to SQL Server type mappings
• Limitations and unsupported features in mssql-django
• JSONField wiki
• Django JSONField documentation