如何使用新的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 < a href = " http://www.postgresql.org/docs/devel/static/datatype-json.html " > < / >
  • http://www.postgresql.org/docs/devel/static/functions-json.html < a href = " 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文档存储

248288 次浏览
小开
最佳答案

Postgres 9.2

我引用安德鲁·邓斯坦在pgsql黑客名单上:

在某些阶段可能会有一些json处理(相反

)函数，但在9.2中没有

这并不妨碍他在PLV8中提供一个示例实现来解决您的问题。(Link现在已死，请参阅modern PLV8。)

Postgres 9.3

提供了一个新函数和操作符库来添加“json-processing”。

Postgres 9.3中最初的问题的答案:

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

先进的例子:

对于更大的表，你可能想要添加一个表达式索引来提高性能:

Postgres 9.4

增加jsonb (b代表"binary"，值存储为原生Postgres类型)和这两个类型的更多功能。除了上面提到的表达式索引，jsonb还支持GIN, btree和哈希索引，其中GIN是最有效的。

手册甚至建议:

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

大胆强调我的。

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

Postgres 9.5

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

小开

在Postgres 9.3+中，只需使用->操作符。例如,

SELECT data->'images'->'thumbnail'->'url' AS thumb FROM instagram;

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

小开

在postgres 9.3中使用->进行对象访问。4例

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)

提交答案