我正在寻找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文档存储
在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)
在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)
Postgres 9.2
我引用Andrew Dunstan在pgsql-hackers列表上的话:
在某些阶段，可能会有一些json处理(相反
生成json)函数，但在9.2中没有。
这并不妨碍他在PLV8中提供一个示例实现来解决您的问题。(Link现在已经死了，取而代之的是现代PLV8。)
Postgres 9.3
提供了一个新函数和操作符库来添加“json-processing”。
关于新JSON功能的手册。
Postgres Wiki在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';
先进的例子:
以JSON数据类型查询嵌套记录数组的组合
对于更大的表，你可能想要添加一个表达式索引来提高性能:
用于在JSON数组中查找元素的索引
邮政 9.4
增加了jsonb (b代表“二进制”，值存储为原生Postgres类型)，以及这两种类型的更多功能。除了上面提到的表达式索引，jsonb还支持GIN、btree和散列索引，其中GIN是最有效的。
json和jsonb数据类型和函数的手册。
9.4页JSONB上的Postgres Wiki
手册甚至建议:
一般来说，大多数应用程序应该倾向于将JSON数据存储为
Jsonb，除非有非常特殊的需求，比如遗留问题
关于对象键排序的假设。
大胆强调我的。
性能得益于GIN索引的一般改进。
Postgres 9.5
完成jsonb函数和操作符。添加更多的函数来操作jsonb并显示出来。
Postgres 9.5发布说明中的重大好消息。
