QuerySet.bulk_update() fails with VARIANT / JSON Field

[stanorama]

Bulk update creates SQL that fails with the following error:

SQL compilation error: Expression type does not match column data type, expecting VARIANT but got VARCHAR(134217728) for column JSON_DATA

Bulk Insert and normal update have no issues.

The following BulkTest Model and Manager generate the following SQL statements, with the 3rd one causing the issue.
I assume the CASE WHEN logic causes the JSON data to treated as a VARCHAR, where as in the regular update the SQL parser knows it will be geeting an object.
Can the SQL be manipulated in this package to utitlise a PARSE_JSON to force the correct data type?

INSERT INTO "PUBLIC"."BULK_TEST" ("BULK_ID", "JSON_DATA") SELECT $1, parse_json($2) FROM VALUES ('d96f7ceec8d34881a5790c70b157c9e2', '{"test": {"key1": 1, "key2": 1}}'), ('9791d23ef765450d8db69dbebdeaa8dc', '{"test": {"key1": 2, "key2": 2}}')

UPDATE "PUBLIC"."BULK_TEST" SET "JSON_DATA" = '{"test": {"key1": 3, "key2": 3}}' WHERE "PUBLIC"."BULK_TEST"."BULK_ID" = 'd96f7ceec8d34881a5790c70b157c9e2'

UPDATE "PUBLIC"."BULK_TEST" SET "JSON_DATA" = CASE WHEN ("PUBLIC"."BULK_TEST"."BULK_ID" = 'd96f7ceec8d34881a5790c70b157c9e2') THEN '{"test": {"key1": 1, "key2": 1}}' WHEN ("PUBLIC"."BULK_TEST"."BULK_ID" = '9791d23ef765450d8db69dbebdeaa8dc') THEN '{"test": {"key1": 2, "key2": 2}}' ELSE NULL END WHERE "PUBLIC"."BULK_TEST"."BULK_ID" IN ('d96f7ceec8d34881a5790c70b157c9e2', '9791d23ef765450d8db69dbebdeaa8dc')

from django.db import models
from uuid import uuid4

# CREATE TABLE PUBLIC.BULK_TEST(
#     BULK_ID VARCHAR(36) NOT NULL,
#     JSON_DATA VARIANT NOT NULL,
#     CONSTRAINT PK_BULK_TEST PRIMARY KEY (BULK_ID)
# );


# BulkTest.objects.test()
class BulkTestManager(models.Manager):
    def test(self):
        bulk_1_id = uuid4()
        bulk_1 = BulkTest(bulk_id=bulk_1_id, json_data={"test": {"key1": 1, "key2": 1}})
        bulk_2 = BulkTest(bulk_id=uuid4(), json_data={"test": {"key1": 2, "key2": 2}})
        self.bulk_create([bulk_1, bulk_2])

        self.filter(bulk_id=bulk_1_id).update(
            json_data={"test": {"key1": 3, "key2": 3}}
        )

        self.bulk_update([bulk_1, bulk_2], fields=["json_data"])


class BulkTest(models.Model):
    bulk_id = models.UUIDField(
        primary_key=True,
        editable=True,
    )

    json_data = models.JSONField()

    objects: BulkTestManager = BulkTestManager()

    class Meta:
        managed = False
        db_table = "PUBLIC.BULK_TEST"
        ordering = ["bulk_id"]

Using
Python==3.11.13
Django==5.1.6
django-snowflake==5.1
snowflake-connector-python==3.16.0

[timgraham]

Yes, it's a documented limitation in the README: QuerySet.bulk_update() isn't supported for JSONField.

When I wrote that, I probably thought that it's either unfeasible or very difficult to implement it. I'll have to check whether snowflake-connector-python will support VARIANT any time soon so we don't need the parse_json() hack.

[shawnbot]

I figured out a workaround for this, which works for me in Django 4.x. First, subclass Django's JSONField like so:

class SnowflakeJSONField(models.JSONField):
    def get_placeholder(self, val, compiler, conn):
        if conn.vendor == "snowflake" and isinstance(
            compiler, SQLUpdateCompiler
        ):
            return "PARSE_JSON(%s)"
        return "%s"

The, replace JSONField(...) in your models with SnowflakeJSONField(...) and migrate (which will be a no-op on the database because it won't change anything about the column definition).

Here's where it's called in SQLUpdateCompiler.as_sql(). The conn.vendor check preserves compatibility with other engines. The compiler type check is necessary because django-snowflake's own SQLInsertCompiler addresses this problem already. It might be possible to drop django-snowflake's custom SQLInsertCompiler class altogether (unless it's doing other stuff, of course) along with the compiler type check above to wrap the placeholders in both query types.

[timgraham]

Thanks for that. I'll reopen this to consider your suggestion if proper support for VARIANT isn't forthcoming in snowflake-connector-python.