django orm date field to get years

Using Django Using the ORM
1

Hi ,
I am trying to convert the rawsql to django orm but i am having issue with the extracting the year value from the Date field , which always gives an

SELECT
COALESCE(x.cust_area_data->>‘Network’, ‘Unknown’) AS Team,
COALESCE(EXTRACT(YEAR FROM (dx.cust_aread_data->>‘SOP’)::DATE), 9000) AS year,
COUNT(*) AS count
FROM
dcim_device x
JOIN
dcim_devicetype dx ON d.device_type_id = dt.id
GROUP BY
COALESCE(d.cust_area_data->>‘Network’, ‘Unknown’),
COALESCE(EXTRACT(YEAR FROM (dt.cust_area_data->>‘SOP’)::DATE), 9999)
ORDER BY
Network,
year;

Django orm

from django.db.models import Count, Value, F, Func, ExpressionWrapper, IntegerField, DateField
from django.db.models.functions import Coalesce, ExtractYear, Cast

YearList = Device.objects.values(year=ExtractYear(‘dev_type__cust_area_data__SOP’)).distinct

always giving

ValueError
Exception Value:
Extract input expression must be DateField, DateTimeField, TimeField, or DurationField.

appreciate your suggestion

2

You didn’t include your models but I assume your cust_aread_data field is a JSONField.

The part you didn’t include in your ORM query is the ::DATE cast part which you can achieve by doing

from django.db.models.fields.json import KeyTextTransform
from django.db.models.functions import Cast

Device.objects.values(
    year=ExtractYear(
        Cast(KeyTextTransform('SOP', F('dev_type__cust_area_data')), DateField())
    )
)
3

Thanks KeyTextTransform method works well