JSONField filter doesn't show up anything

Question

I am trying to filter a JSONField database according to the country code of the database objects. I have the following JSON dictionary in my JSON field database:

"[{\"date\":\"2020-01-01\",\"localName\":\"Neujahr\",\"name\":\"New Year's Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":true,\"counties\":null,\"launchYear\":1967,\"type\":\"Public\"},{\"date\":\"2020-01-06\",\"localName\":\"Heilige Drei K\u00f6nige\",\"name\":\"Epiphany\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":false,\"counties\":[\"DE-BW\",\"DE-BY\",\"DE-ST\"],\"launchYear\":1967,\"type\":\"Public\"},{\"date\":\"2020-03-08\",\"localName\":\"Internationaler Frauentag\",\"name\":\"International Women's Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":false,\"counties\":[\"DE-BE\"],\"launchYear\":2019,\"type\":\"Public\"},{\"date\":\"2020-04-10\",\"localName\":\"Karfreitag\",\"name\":\"Good Friday\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":true,\"counties\":null,\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-04-12\",\"localName\":\"Ostersonntag\",\"name\":\"Easter Sunday\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":false,\"counties\":[\"DE-BB\",\"DE-HE\"],\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-04-13\",\"localName\":\"Ostermontag\",\"name\":\"Easter Monday\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":true,\"counties\":null,\"launchYear\":1642,\"type\":\"Public\"},{\"date\":\"2020-05-01\",\"localName\":\"Tag der Arbeit\",\"name\":\"Labour Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":true,\"counties\":null,\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-05-08\",\"localName\":\"Tag der Befreiung\",\"name\":\"Liberation Day\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":false,\"counties\":[\"DE-BE\"],\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-05-21\",\"localName\":\"Christi Himmelfahrt\",\"name\":\"Ascension Day\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":true,\"counties\":null,\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-05-31\",\"localName\":\"Pfingstsonntag\",\"name\":\"Pentecost\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":false,\"counties\":[\"DE-BB\",\"DE-HE\"],\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-06-01\",\"localName\":\"Pfingstmontag\",\"name\":\"Whit Monday\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":true,\"counties\":null,\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-06-11\",\"localName\":\"Fronleichnam\",\"name\":\"Corpus Christi\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":false,\"counties\":[\"DE-BW\",\"DE-BY\",\"DE-HE\",\"DE-NW\",\"DE-RP\",\"DE-SL\"],\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-08-15\",\"localName\":\"Mari\u00e4 Himmelfahrt\",\"name\":\"Assumption Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":false,\"counties\":[\"DE-SL\"],\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-09-20\",\"localName\":\"Weltkindertag\",\"name\":\"World Children's Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":false,\"counties\":[\"DE-TH\"],\"launchYear\":2019,\"type\":\"Public\"},{\"date\":\"2020-10-03\",\"localName\":\"Tag der Deutschen Einheit\",\"name\":\"German Unity Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":true,\"counties\":null,\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-10-31\",\"localName\":\"Reformationstag\",\"name\":\"Reformation Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":false,\"counties\":[\"DE-BB\",\"DE-MV\",\"DE-SN\",\"DE-ST\",\"DE-TH\",\"DE-HB\",\"DE-HH\",\"DE-NI\",\"DE-SH\"],\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-11-01\",\"localName\":\"Allerheiligen\",\"name\":\"All Saints' Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":false,\"counties\":[\"DE-BW\",\"DE-BY\",\"DE-NW\",\"DE-RP\",\"DE-SL\"],\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-11-18\",\"localName\":\"Bu\u00df- und Bettag\",\"name\":\"Repentance and Prayer Day\",\"countryCode\":\"DE\",\"fixed\":false,\"global\":false,\"counties\":[\"DE-SN\"],\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-12-25\",\"localName\":\"Erster Weihnachtstag\",\"name\":\"Christmas Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":true,\"counties\":null,\"launchYear\":null,\"type\":\"Public\"},{\"date\":\"2020-12-26\",\"localName\":\"Zweiter Weihnachtstag\",\"name\":\"St. Stephen's Day\",\"countryCode\":\"DE\",\"fixed\":true,\"global\":true,\"counties\":null,\"launchYear\":null,\"type\":\"Public\"}]"

So, when I run Database.objects.first().data I get exactly that and it's all good. I have the JSONField defined in the models.py of my Django application:

from django.db import models

class Database(models.Model):
    data = models.JSONField()

However, when I run Database.objects.filter(data__countryCode = "DE") I get <QuerySet []>, and when I also put .data, it says that 'QuerySet' object has no module data. How can I get the filter to work for this database?

Edit: I have been asked to provide more code. The only thing to know is that I extracted that data from https://date.nager.at/, with its documentation at https://date.nager.at/swagger/index.html so that the data there is saved to a JSON file, then to my JSONField database. Here is my views.py:

def get_public_holidays(request, year, tag):
    response = requests.get(f'https://date.nager.at/api/v2/PublicHolidays/{year}/{tag}')
    with open(f'public_holidays_{tag}_{year}.json', 'w') as outfile:
        json.dump(response.text, outfile)
    text_to_add = models.Database(data = response.text)
    text_to_add.save()
    return HttpResponse('Please check the virtual environment folder for the public holidays of the assigned country.')

score 0 · Answer 1 · answered Jan 19 '22 at 11:57

0

I have found SO Question with similar problem, maybe you can try solution from there:

Database.objects.filter(data__0__countryCode = "DE")

answered Jan 19 '22 at 11:57

NixonSparrow

6,130
1
6
18

Found that link too, it still shows query set 0. – Vlad Safta Jan 19 '22 at 12:09
You need to share more code then I suppose. It's hard to point where mistake might be. – NixonSparrow Jan 19 '22 at 12:18

score 0 · Answer 2 · answered Jan 19 '22 at 15:12

With this query you will return the Database object instance with all the json inserted. To make this query you should take the date value and search like a normal dict

    aux = {}  # filtered values
    for json_dict in Database.objects.first().data:
         if json_dict.get('countryCode', '') == 'DE':
             aux.update(json_dict)

You will not be able to filter values of field from a model in the database query

JSONField filter doesn't show up anything

2 Answers2