如何在 Django 中使用 PostgreSQL 9.2 JSON 数据类型？

How do I use PostgreSQL 9.2 JSON data type in Django?

提问by TaiwanGrapefruitTea

PostgreSQL 9.2 has native json data type support. How do i create a django model that can use this data type as one of the model fields?

PostgreSQL 9.2 具有原生 json 数据类型支持。如何创建可以使用此数据类型作为模型字段之一的 Django 模型？

回答by Stefano

[Nb. A lot happened since the question was posted so I thought updating the answer too - now including new Django-postgresql and PostgreSQL 9.3 information]

[注。自从发布问题以来发生了很多事情，所以我也想更新答案 - 现在包括新的 Django-postgresql 和 PostgreSQL 9.3 信息]

PostgreSQL 9.3

PostgreSQL 9.3

JSON support in PostgreSQL(see the docis becoming more and more interesting. It's now possible to search on the JSON fields as if they were database columns! This support is still basic and not on par with standard column operators (see before), as such it's going to be fidgely to use through Django ORM.

PostgreSQL 中的 JSON 支持（参见文档变得越来越有趣。现在可以像搜索数据库列一样搜索 JSON 字段！这种支持仍然是基本的，与标准的列运算符（见前文）不相提并论），如因此通过 Django ORM 使用它会很麻烦。

PostgreSQL 9.2

PostgreSQL 9.2

The JSON data type is basically textin pgSQL 9.2. What's added is JSON validation - useful, but not head turning.

JSON 数据类型在 pgSQL 9.2 中基本上是文本。添加的是 JSON 验证 - 有用，但不会让人头疼。

**Django and PostgreSQL JSON

**Django 和 PostgreSQL JSON

A recent Kickstarted backed developmentfor advanced PostgreSQL support in Django was funded a few months ago. It will include advanced support for the JSON type, but probably only in 9.4:

最近Kickstarted支持的 Django 高级 PostgreSQL 支持开发是在几个月前获得资助的。它将包括对 JSON 类型的高级支持，但可能仅在 9.4 中：

The Postgres team have recently merged support for a jsonb datatype - binary stored JSON. It is quite likely that I will delay JSON support until Postgres 9.4 is out and only support the jsonb data type. There are several reasons for this, the most significant being that the current json data type is severly limited in its implementation, lacking even an equality operator. This means that some parts of Django annotation code generate invalid queries (see this report) and also means that a __exact lookup has to be forbidden. To handle all these edge cases properly in Django would result in a huge amount of complexity, and the benefits you gain over just storing json in a text field are actually quite limited. 9.4 is due out towards the end of this year, so as a result JSON fields are likely to only feature in the 1.8 release.

Postgres 团队最近合并了对 jsonb 数据类型的支持——二进制存储的 JSON。我很可能会延迟 JSON 支持，直到 Postgres 9.4 发布并且只支持 jsonb 数据类型。这有几个原因，最重要的是当前的 json 数据类型在其实现方面受到严重限制，甚至缺少相等运算符。这意味着 Django 注释代码的某些部分会生成无效查询（请参阅此报告），并且还意味着必须禁止 __exact 查找。在 Django 中正确处理所有这些边缘情况会导致大量的复杂性，并且您获得的仅将 json 存储在文本字段中的好处实际上非常有限。9.4 将在今年年底发布，因此 JSON 字段很可能只在 1.8 版本中提供。

Source: mjtamlyn blog

来源：mjtamlyn 博客

Psycopgalso now supports natively the JSON field(as well as HSTORE).

Psycopg现在还原生支持JSON 字段（以及 HSTORE）。

** DIY in Django **

** 在 Django 中 DIY **

In django it's pretty easy to create your own model field that uses a special Database type, see field db_type().

在 django 中，创建自己的使用特殊数据库类型的模型字段非常容易，请参阅字段 db_type()。

Please notice that:

请注意：

1. this would only work under pgSQL - you are limiting your code portability (though you can conditionally return a 'text' type on other databases)
2. you will currently mostly gain only database-side json validation
3. and of course the pleasure of retrieving directly a JSON object!

1. 这只能在 pgSQL 下工作-您限制了代码的可移植性（尽管您可以在其他数据库上有条件地返回“文本”类型）
2. 您目前只会获得数据库端 json 验证
3. 当然还有直接检索 JSON 对象的乐趣！

But be careful that Python do not have /direct/ support for JSON, so you are not really skipping the json dump/load cost in python; you do avoid string conversion - but I haven't checked the internals or benchmarked to see if there is any real difference.

但是要注意 Python 没有 /direct/ 对 JSON 的支持，因此您并没有真正跳过 Python 中的 json 转储/加载成本；您确实避免了字符串转换 - 但我没有检查内部结构或进行基准测试以查看是否有任何真正的区别。

With PostgreSQL 9.3 and following looks like things might become more interesting

使用 PostgreSQL 9.3 和以下看起来事情可能会变得更有趣

You could also start from the django JSONFieldand get a lot of boilercode plate already, simply override the db_typemethod.

您也可以从django JSONField开始并获得大量样板代码，只需覆盖该db_type方法即可。

What sounds, in my opinion, more interesting and worth a database lock-in (I love pgSql, in any case!), is the option in pgSQL to retun data as a JSON structure using array_to_jsonand row_to_json. This would require a more serious change to querysets though, which sounds out of the scope of your question.

在我看来，听起来更有趣并且值得锁定数据库（无论如何我都喜欢 pgSql！），是 pgSQL 中的选项可以使用array_to_json和row_to_json将数据重新调整为 JSON 结构。不过，这将需要对查询集进行更严重的更改，这听起来超出了您的问题范围。

回答by bjudson

As of Django 1.9, JSONFieldis available in the django.contrib.postgres.fieldsmodule that ships with Django. This field uses PostgreSQL field type jsonb(not json).

从 Django 1.9 开始，JSONField可在django.contrib.postgres.fieldsDjango 附带的模块中使用。此字段使用 PostgreSQL 字段类型jsonb（不是 json）。