我正在为 PostgreSQL 9.2 中的新 JSON 函数寻找一些文档和/或示例。
具体来说,给定一系列 JSON 记录:
[ {name: "Toby", occupation: "Software Engineer"}, {name: "Zaphod", occupation: "Galactic President"} ]
我将如何编写 SQL 以按名称查找记录?
在普通 SQL 中:
SELECT * from json_data WHERE "name" = "Toby"
官方开发手册非常稀少:
我整理了一个要点,详细说明了 PostgreSQL 9.2 目前可能实现的功能。使用一些自定义函数,可以执行以下操作:
SELECT id, json_string(data,'name') FROM things WHERE json_string(data,'name') LIKE 'G%';
我现在已经将我的 JSON 函数移到了他们自己的项目中:
PostSQL - 一组用于将 PostgreSQL 和 PL/v8 转换为非常棒的 JSON 文档存储的函数
我在 pgsql-hackers 列表中引用了 Andrew Dunstan:
在某个阶段可能会有一些 json 处理(而不是 json 生成)功能,但在 9.2 中没有。
不妨碍他在 PLV8 中提供一个可以解决您的问题的示例实现。(链接现已失效,请参阅现代PLV8。)
提供一系列新功能和运算符以添加“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';
高级示例:
对于较大的表,您可能需要添加表达式索引以提高性能:
添加(b 表示“二进制”,值存储为本机 Postgres 类型)以及 两种 jsonb 类型的更多功能。除了上面提到的表达式索引,还支持GIN、btree 和哈希索引,其中 GIN 是最强大的。 __jsonb
jsonb
json
该手册建议:
一般来说, 大多数应用程序应该更喜欢将 JSON 数据存储为jsonb,除非有非常特殊的需求,例如关于对象键排序的遗留假设。
大胆强调我的。
性能受益于 GIN 索引的全面改进。
功能齐全jsonb,算子齐全。添加更多功能以jsonb进行就地操作和显示。