2

I am wondering what is the difference in efficiency using JSONFields vs. a pure SQL approach in my Postgres DB.

I now know that I can query JSONFields like this:

MyModel.objects.filter(json__title="My title")

In pure SQL, it would look like this:

MyModel.objects.filter(title="My title")

Are these equal in efficiency?

Improve this question

1 Answer 1

Reset to default
4

Having separate columns for each thing is definitely more efficient.

The advantage of a JSONField is flexibility. You can store anything you want in there, and you don't have to change your database schema. But this comes at a cost. If you have a column that is a CharField with max 255 characters for example, then lots of time and effort will have gone into making a database that can optimise for that particular type (likewise for other types). With a JSONField however, it can be literally anything and it becomes very difficult to optimise a query (at the actual database level) for this.

Unless you have a good reason to use a JSON field (namely you need that level of flexibility) it is much much much better to go with separate columns for each of your fields. There are other advantages besides performance as well. You can define defaults, you can know for certain what types different variables are, which will make programming with them a whole heap easier and avoid a load of errors.

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

7 Comments

Thank you for your response. This makes sense. For my specific task, I found a way to use pure SQL instead of JSON, however, it uses many tables (almost 50). As opposed to one table with a JSON field. Would it still be more efficient to use JSON? Thanks!!
Why does it require so many tables? Why not just have lots of columns on one table?
Basically, I am building a Google Forms clone and the only way I can think of to support different types of questions (such as Input box, multiple choice, checkboxes, etc.) is to make a table for each type. Thus, I need one table for each type of question, and I have almost 50. Does this make sense? Thanks
Yes, I think it will still be more efficient. More importantly, I think it will be much much easier if you separate out things, rather than try and model every eventuality with a single JSONField. You can have special methods for each model etc.
I see. Is there a number of tables, X, where the JSON will be more efficient? Or will the SQL approach always be better?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.