我正在为 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/functions-json.html

更新我

我整理了一个 gist detailing what is currently possible with PostgreSQL 9.2 . 使用一些自定义函数，可以执行以下操作:

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

更新二

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

PostSQL - 一组用于将 PostgreSQL 和 PL/v8 转换为非常棒的 JSON 文档存储的函数

最佳答案

Postgres 9.2

我引用 Andrew Dunstan on the pgsql-hackers list :

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

并不妨碍他在 PLV8 中提供一个可以解决您的问题的示例实现。 (链接已失效，请参阅现代版 PLV8。)

Postgres 9.3

提供大量新函数和运算符以添加“json-processing”。

• The manual on new JSON functionality.
• The Postgres Wiki on new features in pg 9.3 .

Postgres 9.3 中原始问题的答案:

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

高级示例:

• Query combinations with nested array of records in JSON datatype

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

• Index for finding an element in a JSON array

Postgres 9.4

添加 jsonb(b 表示“二进制”，值存储为原生 Postgres 类型)以及 both 类型的更多功能。除了上面提到的表达式索引，jsonb还支持GIN, btree and hash indexes , GIN 是其中最有效的。

• json and jsonb data types 上的手册和 functions .
• The Postgres Wiki on JSONB in pg 9.4

本手册建议:

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.

我的大胆强调。

Performance benefits from general improvements to GIN indexes.

Postgres 9.5

完整的jsonb函数和操作符。添加更多函数来操作 jsonb 并显示。

• Major good news in the release notes of Postgres 9.5.

https://stackoverflow.com/questions/10560394/