1. 首页
  2. 问题
  3. 如何使用新的 PostgreSQL JSON 数据类型中的字段进行查询？
小编典典

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

all

我正在为 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 文档存储的函数


阅读 411

收藏
2022-06-06

共1个答案

小编典典

Postgres 9.2

我在 pgsql-hackers 列表中引用了 Andrew
Dunstan：

在某个阶段可能会有一些 json 处理（而不是 json 生成）功能，但在 9.2 中没有。

不妨碍他在 PLV8 中提供一个可以解决您的问题的示例实现。（链接现已失效，请参阅现代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

添加（b 表示“二进制”，值存储为本机 Postgres 类型）以及 两种 jsonb
类型的更多功能。除了上面提到的表达式索引，还支持GIN、btree
和哈希索引，其中 GIN 是最强大的。
__jsonb

该手册建议：

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

大胆强调我的。

性能受益于 GIN
索引的全面改进。

Postgres 9.5

功能齐全jsonb，算子齐全。添加更多功能以jsonb进行就地操作和显示。

2022-06-06