如何使用新 PostgreSQL JSON 数据类型中的字段进行查询？

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

具体来说，给定一系列 JSON 记录：

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

我该如何编写 SQL 来按名称查找记录？

在普通 SQL 中：

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

官方的开发手册非常稀疏：

• http://www.postgresql.org/docs/devel/static/datatype-json.html 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/functions-json.html

Update I

我整理了一个gist 详细介绍了 PostgreSQL 9.2 当前的功能 https://gist.github.com/2715918。 使用一些自定义函数，可以执行以下操作：

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

更新二

我现在已将 JSON 函数移至他们自己的项目中：

PostSQL https://github.com/tobyhede/postsql- 一组用于将 PostgreSQL 和 PL/v8 转换为非常棒的 JSON 文档存储的函数

Postgres 9.2

I quote Andrew Dunstan 在 pgsql-hackers 名单上 https://archives.postgresql.org/pgsql-hackers/2012-01/msg01764.php:

在某个阶段可能会有一些 json 处理（而不是 到 json 生成）函数，但在 9.2 中没有。

并不妨碍他提供 PLV8 中的示例实现来解决您的问题。 （链接现已失效，请参阅现代PLV8 https://plv8.github.io/反而。）

Postgres 9.3

提供一系列新函数和运算符来添加“json-processing”。

• 新 JSON 功能的手册。 https://www.postgresql.org/docs/9.3/functions-json.html
• Postgres Wiki 关于 pg 9.3 中的新功能 https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#JSON:_Additional_functionality.

答案是原问题在 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 数据类型中的嵌套记录数组的查询组合 https://stackoverflow.com/questions/26877241/query-combinations-with-nested-array-of-records-in-json-datatype/26880705#26880705

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

• 用于在 JSON 数组中查找元素的索引 https://stackoverflow.com/questions/18404055/index-for-finding-element-in-json-array

Postgres 9.4

Adds jsonb（b 表示“二进制”，值存储为本机 Postgres 类型）以及更多功能both类型。除了上面提到的表达式索引之外，jsonb也支持GIN、btree 和哈希索引 https://www.postgresql.org/docs/9.4/datatype-json.html#JSON-INDEXING，GIN 是其中最有效的。

• 手册上json and jsonb数据类型 https://www.postgresql.org/docs/9.4/datatype-json.html and 功能 https://www.postgresql.org/docs/current/functions-json.html.
• Postgres Wiki 关于 JSONB 的第 9.4 页 https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#JSONB_Binary_JSON_storage

该手册甚至建议：

一般来说，大多数应用程序应该更喜欢将 JSON 数据存储为jsonb，除非有非常特殊的需求，例如遗留 关于对象键排序的假设。

大胆强调我的。

性能受益于 GIN 索引的全面改进。 https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#GIN_indexes_now_faster_and_smaller

Postgres 9.5

完全的jsonb函数和运算符。添加更多操作功能jsonb就位并进行展示。

• Postgres 9.5 发行说明中的​​重大好消息。 https://www.postgresql.org/docs/9.5/release-9-5.html#AEN126466