Filtering in Django against a JsonField which is a JsON Array with dates values in it

Question

I have a model in Django where there is a JsonField named periodicity which serialize in an Json Array like this:

{
  "id_series": 12381,
  "country_name": "Central African Republic",
  "periodicity": [
    {
      "period": "monthly",
      "start_date": "2010-10-01",
      "end_date": "2018-10-01"
    },
    {
      "period": "weekly",
      "start_date": "2011-10-01",
      "end_date": "2018-01-01"
    }
  ]
},
{
...
}

So my aim is to filter and find the series that have not been updated in less than a certain date. For that I need to query on the end_date of the inner Json Array. What is the best way to do a filtering?

I have tried casting the end_date to a date object by doing this :

from django.db.models.expressions import RawSQL
Serie.objects.annotate(endDate=RawSQL("((periodicity->>'end_date')::date)", [])).filter(endDate__gte=comparisonDate)

But so far it has not produced any result. I am looking into something like this https://stackoverflow.com/a/65184218/2219080.

Barbaros Özhan · Accepted Answer · 2021-06-05 16:05:49Z

0

The similar logic might be used in order the extract the elements of the periodicity array through JSON_ARRAY_ELEMENTS() function and filtering out through TO_DATE() casting such as

SELECT t.jsdata ->> 'id_series' AS id_series, 
       t.jsdata ->> 'country_name' AS country_name,
       j.value ->> 'period' AS period
  FROM t,
       JSON_ARRAY_ELEMENTS(jsdata->'periodicity') AS j
 WHERE TO_DATE(j.value->>'end_date','YYYY-MM-DD') >=  '2018-01-01'

Demo

answered Jun 5, 2021 at 16:05

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iMitwe Over a year ago

Thanks you. I'll try it out soon and let you know.

Collectives™ on Stack Overflow

Filtering in Django against a JsonField which is a JsON Array with dates values in it

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related