1

I am building a chatbot using Django with a MySQL backend. I have a ChatSession model which represents all the useful information to be stored after the end of a chat session .

Now, it has a JSON field called cc_data(Implemented using django-jsonfield)

Here's an example of the content of that field:

{
  "slots":{
    "chal_tech":"What is the Proof",
    "nats":["I will fail the course","Nobody likes me"],
    "distortion":"Jumping to Conclusions",
    "user_name":"parth",
  }
}

I recently realized that I need to query for some fields in this slots dictionary. An example query would be to list out all the nats in the last 10 chat sessions.

It seems that reading all the values of the JSONs into dicts and then searching/manipulating them would be inefficient.Therefore I sought to convert this JSON representation into Django ORM models(SQL).

However, while trying to do this I ran into a problem. The "keys" of the slots dictionary are probably finite in number, but I don't know exactly how many of them will be required at this point. In any case, it would be a large number (around 50-100).

  • Manually writing the model classes for so many attributes in models.py seems inefficient and also a bit repetitive (Most of them would simply have a single JSONfield or TextField). Even with abstract models I would still have to list out classes for all the "keys" in the models.py file.
  • Ideally I'd like to generate model classes for any new "keys" automatically and on the fly. I did look at dynamic models in Django but none of the solutions really seem efficient. The host I am using doesn't support NoSQL so I can't use that solution.

How do I convert this into valid a valid Django ORM/SQL design so that the queries are efficient and the code isn't repetitive ?

Parth Sharma
  • 441
  • 4
  • 19
  • 2
    Either pick a [Entity-Attribute-Value solution](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) for Django, or use PostgreSQL and [query against Django's JSON field](https://docs.djangoproject.com/en/2.1/ref/contrib/postgres/fields/#querying-jsonfield) – Martijn Pieters Feb 02 '19 at 17:51
  • 1
    Why do you want to generate a new model for a new key? Would not a model with object FK, kind (slot name) and content (whatever you prefer, text or json) be sufficient? – awesoon Feb 02 '19 at 17:51
  • @awesoon That's what I was thinking of doing. However, there is a slight caveat, if content of the slot is JSON itself and I need to search for keys inside that, then I run into the same problem again. – Parth Sharma Feb 03 '19 at 10:41

0 Answers0