如何使用新的PostgreSQL JSON数据类型内的字段进行查询? [英] How do I query using fields inside the new PostgreSQL JSON datatype?

问题描述

我正在寻找PostgreSQL 9.2中新JSON函数的一些文档和/或示例.

I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2.

具体来说，给出一系列JSON记录:

Specifically, given a series of JSON records:

[
  {name: "Toby", occupation: "Software Engineer"},
  {name: "Zaphod", occupation: "Galactic President"}
]

我该如何编写SQL以按名称查找记录?

How would I write the SQL to find a record by name?

在普通SQL中:

SELECT * from json_data WHERE "name" = "Toby"

官方开发人员手册很少:

The official dev manual is quite sparse:

• http://www.postgresql.org/docs/devel/static/datatype-json .html
• http://www.postgresql.org/docs/devel/static/functions-json .html

• http://www.postgresql.org/docs/devel/static/datatype-json.html
• http://www.postgresql.org/docs/devel/static/functions-json.html

我整理了一个要点，详细介绍了PostgreSQL 9.2当前可能实现的功能. 使用一些自定义功能，可以执行以下操作:

I've put together a gist detailing what is currently possible with PostgreSQL 9.2. Using some custom functions, it is possible to do things like:

SELECT id, json_string(data,'name') FROM things
WHERE json_string(data,'name') LIKE 'G%';

更新II

我现在将JSON函数移到了自己的项目中:

Update II

I've now moved my JSON functions into their own project:

PostSQL -一组用于将PostgreSQL和PL/v8转换为完全很棒的JSON的函数文件存储

PostSQL - a set of functions for transforming PostgreSQL and PL/v8 into a totally awesome JSON document store

推荐答案

Postgres 9.2

我在pgsql-hackers列表中引用 Andrew Dunstan :

在某个阶段可能会进行一些json处理(相对于 到产生json的功能)，但在9.2中则没有.

At some stage there will possibly be some json-processing (as opposed to json-producing) functions, but not in 9.2.

不能阻止他提供 PLV8中的示例实现应该可以解决您的问题.

Doesn't prevent him from providing an example implementation in PLV8 that should solve your problem.

提供大量新功能和运算符以添加"json处理".

Offers an arsenal of new functions and operators to add "json-processing".

• 有关JSON新功能的手册.
• 第9.3页的新功能的Postgres Wiki .
• @Will将发布指向的链接在

• The manual on new JSON functionality.
• The Postgres Wiki on new features in pg 9.3.
• @Will posted a link to a blog demonstrating the new operators in a comments below.

Postgres 9.3中的原始问题的答案:

The answer to the original question in Postgres 9.3:

SELECT *
FROM   json_array_elements(
  '[{"name": "Toby", "occupation": "Software Engineer"},
    {"name": "Zaphod", "occupation": "Galactic President"} ]'
  ) AS elem
WHERE elem->>'name' = 'Toby';

高级示例:

• 使用嵌套数组的查询组合JSON数据类型中的记录数量

对于更大的表，您可能需要添加一个表达式索引以提高性能:

For bigger tables you may want to add an expression index to increase performance:

• 在JSON数组中查找元素的索引

添加了 jsonb (b表示二进制"，值存储为本地Postgres类型)，还为两种类型提供了更多功能.除了上面提到的表达式索引，jsonb还支持 GIN，btree和哈希索引，其中GIN最有效.

Adds jsonb (b for "binary", values are stored as native Postgres types) and yet more functionality for both types. In addition to expression indexes mentioned above, jsonb also supports GIN, btree and hash indexes, GIN being the most potent of these.

• 有关 json和jsonb数据类型的手册和功能.
• 第9.4页的JSONB上的Postgres Wiki

• The manual on json and jsonb data types and functions.
• The Postgres Wiki on JSONB in pg 9.4

该手册的建议范围如下:

The manual goes as far as suggesting:

通常，大多数应用程序应首选将JSON数据存储为 jsonb ，除非有非常特殊的需求(例如旧版) 有关对象键顺序的假设.

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

强调粗体.

从GIN索引的总体改进中可以获得性能.

完成jsonb函数和运算符.添加更多功能以操纵jsonb并显示.

Complete jsonb functions and operators. Add more functions to manipulate jsonb in place and for display.

• Postgres 9.5发行说明中的​​主要好消息

这篇关于如何使用新的PostgreSQL JSON数据类型内的字段进行查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！