Convert JSON data into structured data in an SQL database using Django ORM

[Parth Sharma]

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`](https://github.com/dmkoch/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](https://stackoverflow.com/questions/7933596/django-dynamic-model-fields/7934577#7934577) 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 ?

[Mark Phillips]

MySQL and Postgres both support JSON fields directly, and have some searching capability built in for those fields. I don't think you have to use the django-jsonfield anymore. Check the 

MySQL and/or Postgres docs - 

https://dev.mysql.com/doc/refman/8.0/en/json.html

https://www.postgresql.org/docs/9.3/functions-json.html

JSON, Django, and MySQL docs

https://django-mysql.readthedocs.io/en/latest/installation.html

Mark

Mark

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/1bfeb786-9675-45e6-98df-9471ce5391dc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.