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

问题描述

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

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

<预><代码>[{名称:托比"，职业:软件工程师"}，{名称:赞福德"，职业:银河总统"}]

我将如何编写 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

更新一

我整理了一个要点，详细说明了 PostgreSQL 9.2 当前的可能性.使用一些自定义函数，可以执行以下操作:

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

更新二

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

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

解决方案

Postgres 9.2

我引用了 Andrew Dunstan 在 pgsql-hackers 列表中的内容:

<块引用>

在某个阶段可能会有一些 json 处理(而不是到 json-produce) 函数，但不在 9.2 中.

不会阻止他在 PLV8 中提供应该可以解决您的问题的示例实现.(链接现已失效，请改用现代 PLV8.)

Postgres 9.3

提供一系列新函数和运算符来添加json-processing".

• 新 JSON 功能手册.
• 关于 pg 9.3 新功能的 Postgres Wiki.

Postgres 9.3 中原始问题的答案:

SELECT *从 json_array_elements('[{姓名":托比"，职业":软件工程师"}，{名字":赞福德"，职业":银河总统"} ]') 作为元素WHERE elem->>'name' = 'Toby';

高级示例:

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

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

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

Postgres 9.4

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

• json 和 的手册jsonb 数据类型 和 函数.
• 第 9.4 页中 JSONB 上的 Postgres Wiki

手册就建议而言:

<块引用>

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

粗体强调我的.

性能得益于 GIN 索引的全面改进.>

Postgres 9.5

完整的jsonb 函数和操作符.添加更多函数来操作 jsonb 就地和显示.

• Postgres 9.5 发行说明中的​​重大好消息.

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

Specifically, given a series of JSON records:

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

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

In vanilla 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

Update I

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%';

Update II

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

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

解决方案

Postgres 9.2

I quote 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.

Doesn't prevent him from providing an example implementation in PLV8 that should solve your problem. (Link is dead now, see modern PLV8 instead.)

Postgres 9.3

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

• The manual on new JSON functionality.
• The Postgres Wiki on new features in pg 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';

Advanced example:

• Query combinations with nested array of records in JSON datatype

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

• Index for finding an element in a JSON array

Postgres 9.4

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.

• 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:

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.

Bold emphasis mine.

Performance benefits from general improvements to GIN indexes.

Postgres 9.5

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

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

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