Potential bug with how Value works with Django ORM queries

[Nitin Chaudhary]

Hi 

I recently came across a very interesting scenario. I have a JSON Field which I want to query on. I was trying to do some performance optimizations  and came across this

If I query a non-JSON field like this:

Study.objects.all().filter(study_id__in=[Value("Tes1"),Value("Test2")])

and 

Study.objects.all().filter(study_id__in=["Test1","Test2"])

both of these queries return the same results

but if I try performing the same on a JSONField

Study.objects.all().filter(study_data__protocol__general__program_name__in=(Value("Program1"), Value("Program2")))

results in

DataError: invalid input syntax for type json
LINE 1: ...> ARRAY['protocol','general','program_name']) IN ('Prog ...
                                                             ^
DETAIL:  Token "Program1" is invalid.
CONTEXT:  JSON data, line 1: Program1

and 

Study.objects.all().filter(study_data__protocol__general__program_name__in=("Program1","Program2"))

works fine.

I am not sure if this is a potential bug with Django or is like this by design. Would really appreciate if someone can explain this behavior difference.

Cheers

[David Sanders]

Hi Nitin,

Not sure what db you're using but will explain assuming PG.

In this particular example, Django uses the `->` operator which requires that we compare jsonb types.  The `__in=("Program1", "Program2")` is converted to jsonb via `DatabaseOperations.adapt_json_value()` where as `Value()` isn't.

`Value()` assumes the type of the argument passed in – in this case `str` – unless you pass a second argument, `output_field`. Specifying `output_field=JSONField()` will cause the ORM to pass these values to `DatabaseOperations.adapt_json_value()`.

So this will work:

`Study.objects.filter(study_data__protocol__general__program_name__in=(Value("Program1", output_field=JSONField()), ...))`

Regards

David

[Nitin Chaudhary]

Hi David,

Thanks for response. I understand it now. I was assuming that Value auto typecasts to the field that we are querying. This makes sense.

Thank you for taking time to respond.

Cheers

Nitin