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

Question

我正在为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

我总结了一个要点，详细说明了PostgreSQL 9.2目前可能实现的功能。使用一些自定义函数，可以执行以下操作：

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

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

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

Answer 1

与postgres 9.3使用-

seed.rb

se = SmartElement.new
se.data = 
{
    params:
    [
        {
            type: 1,
            code: 1,
            value: 2012,
            description: 'year of producction'
        },
        {
            type: 1,
            code: 2,
            value: 30,
            description: 'length'
        }
    ]
}

se.save

rails c

SELECT data->'params'->0 as data FROM smart_elements;

返回

                                 data
----------------------------------------------------------------------
 {"type":1,"code":1,"value":2012,"description":"year of producction"}
(1 row)

你可以继续筑巢

SELECT data->'params'->0->'type' as data FROM smart_elements;

回来

 data
------
 1
(1 row)

Answer 2

使用Postgres 9.3，只需使用-

选择数据-

见http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/一些很好的例子和教程。

Answer 3

我引用pgsql黑客名单上的安德鲁·邓斯坦的话：

在某个阶段可能会有一些json处理（与json生成相反）函数，但在9.2中没有。

这并不妨碍他在PLV8中提供一个示例实现来解决您的问题。（链接现在已关闭，请参见现代PLV8。）

提供了一系列新的函数和操作符来添加“json处理”。

• 关于新JSON功能的手册
• Postgres Wiki介绍了第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';

高级示例：

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

对于较大的表，您可能希望添加表达式索引以提高性能：

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

为这两种类型添加了jsonb（b代表“二进制”，值存储为原生Postgres类型）和更多功能。除了上面提到的表达式索引，jsonb还支持GIN、btree和hash索引，GIN是其中最强大的。

• 关于json和jsonb数据类型和函数的手册

手册甚至建议：

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

我的。

性能受益于GIN索引的一般改进。

完成jsonb函数和运算符。添加更多功能以就地操作jsonb并进行显示。

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