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
On Thursday, 26 October 2023 at 03:39:08 UTC+11 Nitin Chaudhary wrote:
> 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
>
>
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/95511868-4a44-4757-a992-b4e17c716668n%40googlegroups.com.
