PostgreSQL中文文档网

Appearance

JSON 操作 ​ ​

PostgreSQL 提供了强大的 JSON 数据类型支持，允许开发者在数据库中存储和操作 JSON 数据。通过一系列内建函数和操作符，开发者可以高效地处理 JSON 格式的数据，从而支持更复杂的数据结构。

JSON 数据类型 ​ ​

PostgreSQL 支持两种 JSON 数据类型：

  • JSON：存储 JSON 格式的数据，保留原始格式，不做任何验证。
  • JSONB：存储二进制格式的 JSON 数据，具有更高的处理速度和索引支持，适用于大量读写操作。

示例：创建含有 JSON 字段的表 ​ ​

sql
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  details JSONB
);

JSON 数据查询与提取 ​ ​

PostgreSQL 提供了一些内置的函数和操作符来查询 JSON 数据。

使用 -和 ->提取 JSON 数据 ​ ​

  • ->：提取 JSON 对象中的字段（返回 JSON 类型）。
  • ->>：提取 JSON 对象中的字段（返回文本类型）。

示例：提取 JSON 字段 ​ ​

假设 details 列存储了产品的详细信息，以 JSON 格式存储：

json``` { "price": 100, "colors": ["red", "blue"], "brand": "ExampleBrand" }


```sql
SELECT details->'price' FROM products;
sql
SELECT details->>'price' FROM products;
  • details->'price' 会返回 JSON 格式的数据（例如：100）。
  • details->>'price' 会返回文本格式的数据（例如：'100'）。

使用 #和 #>提取嵌套 JSON 数据 ​ ​

  • #>：提取嵌套 JSON 对象中的字段（返回 JSON 类型）。
  • #>>：提取嵌套 JSON 对象中的字段（返回文本类型）。

示例：提取嵌套字段 ​ ​

假设 details 列中的 JSON 结构如下：

json``` { "price": 100, "variants": { "color": "red", "size": "M" } }


```sql
SELECT details#>>'{variants,color}' FROM products;
  • details#>>'{variants,color}' 提取嵌套字段 variants.color，返回 "red"。

JSON 数组操作 ​ ​

PostgreSQL 提供了对 JSON 数组的操作支持，能够查询数组元素、更新数组等。

使用 -和 ->提取数组元素 ​ ​

假设 details 中包含一个颜色数组：

json``` { "colors": ["red", "blue", "green"] }


#### 示例：提取数组元素 ​

```sql
SELECT details->'colors'->>0 FROM products;

这将返回数组中的第一个元素：'red'。

使用 jsonb_array_length() 获取数组长度 ​ ​

sql
SELECT jsonb_array_length(details->'colors') FROM products;

这将返回 colors 数组的长度。

使用 jsonb_array_elements() 展开数组 ​ ​

sql
SELECT jsonb_array_elements(details->'colors') FROM products;

这将展开 colors 数组中的每个元素，并返回每个元素作为单独的一行。

JSON 修改与更新 ​ ​

PostgreSQL 提供了多种函数用于修改 JSON 数据。

使用 jsonb_set() 更新 JSON 数据 ​ ​

jsonb_set() 函数用于修改 JSON 中的值。

sql
UPDATE products
SET details = jsonb_set(details, '{price}', '120'::jsonb)
WHERE id = 1;

这将更新 id 为 1 的产品的 price 字段为 120。

使用 jsonb_insert() 插入新元素 ​ ​

jsonb_insert() 函数用于向 JSON 数组中插入新元素。

sql
UPDATE products
SET details = jsonb_insert(details, '{colors,0}', '"yellow"')
WHERE id = 1;

这将在 colors 数组的开头插入 "yellow"。

使用 jsonb_delete() 删除字段或数组元素 ​ ​

jsonb_delete() 用于从 JSON 数据中删除指定的字段或数组元素。

sql
UPDATE products
SET details = jsonb_delete(details, '{colors,1}')
WHERE id = 1;

这将删除 colors 数组中的第二个元素（即 "blue"）。

JSON 索引 ​ ​

为了提高查询性能，PostgreSQL 支持对 JSON 数据创建索引。对于 JSONB 数据类型，推荐使用 GIN（广义倒排索引）索引。

示例：为 JSONB 列创建索引 ​ ​

sql
CREATE INDEX idx_products_details ON products USING GIN (details);

小结 ​ ​

PostgreSQL 提供了强大的 JSON 数据类型支持，使得开发者能够灵活地存储、查询和修改 JSON 数据。通过内置的函数和操作符，开发者可以高效地操作 JSON 字段，并结合索引优化查询性能。掌握 JSON 操作将使得开发者能够处理更为复杂和动态的数据结构。

JSON 操作 ​ ​

PostgreSQL 提供了强大的 JSON 数据类型支持，允许开发者在数据库中存储和操作 JSON 数据。通过一系列内建函数和操作符，开发者可以高效地处理 JSON 格式的数据，从而支持更复杂的数据结构。

JSON 数据类型 ​ ​

PostgreSQL 支持两种 JSON 数据类型：

  • JSON：存储 JSON 格式的数据，保留原始格式，不做任何验证。
  • JSONB：存储二进制格式的 JSON 数据，具有更高的处理速度和索引支持，适用于大量读写操作。

示例：创建含有 JSON 字段的表 ​ ​

sql
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  details JSONB
);

JSON 数据查询与提取 ​ ​

PostgreSQL 提供了一些内置的函数和操作符来查询 JSON 数据。

使用 -和 ->提取 JSON 数据 ​ ​

  • ->：提取 JSON 对象中的字段（返回 JSON 类型）。
  • ->>：提取 JSON 对象中的字段（返回文本类型）。

示例：提取 JSON 字段 ​ ​

假设 details 列存储了产品的详细信息，以 JSON 格式存储：

json``` { "price": 100, "colors": ["red", "blue"], "brand": "ExampleBrand" }


```sql
SELECT details->'price' FROM products;
sql
SELECT details->>'price' FROM products;
  • details->'price' 会返回 JSON 格式的数据（例如：100）。
  • details->>'price' 会返回文本格式的数据（例如：'100'）。

使用 #和 #>提取嵌套 JSON 数据 ​ ​

  • #>：提取嵌套 JSON 对象中的字段（返回 JSON 类型）。
  • #>>：提取嵌套 JSON 对象中的字段（返回文本类型）。

示例：提取嵌套字段 ​ ​

假设 details 列中的 JSON 结构如下：

json``` { "price": 100, "variants": { "color": "red", "size": "M" } }


```sql
SELECT details#>>'{variants,color}' FROM products;
  • details#>>'{variants,color}' 提取嵌套字段 variants.color，返回 "red"。

JSON 数组操作 ​ ​

PostgreSQL 提供了对 JSON 数组的操作支持，能够查询数组元素、更新数组等。

使用 -和 ->提取数组元素 ​ ​

假设 details 中包含一个颜色数组：

json``` { "colors": ["red", "blue", "green"] }


#### 示例：提取数组元素 ​

```sql
SELECT details->'colors'->>0 FROM products;

这将返回数组中的第一个元素：'red'。

使用 jsonb_array_length() 获取数组长度 ​ ​

sql
SELECT jsonb_array_length(details->'colors') FROM products;

这将返回 colors 数组的长度。

使用 jsonb_array_elements() 展开数组 ​ ​

sql
SELECT jsonb_array_elements(details->'colors') FROM products;

这将展开 colors 数组中的每个元素，并返回每个元素作为单独的一行。

JSON 修改与更新 ​ ​

PostgreSQL 提供了多种函数用于修改 JSON 数据。

使用 jsonb_set() 更新 JSON 数据 ​ ​

jsonb_set() 函数用于修改 JSON 中的值。

sql
UPDATE products
SET details = jsonb_set(details, '{price}', '120'::jsonb)
WHERE id = 1;

这将更新 id 为 1 的产品的 price 字段为 120。

使用 jsonb_insert() 插入新元素 ​ ​

jsonb_insert() 函数用于向 JSON 数组中插入新元素。

sql
UPDATE products
SET details = jsonb_insert(details, '{colors,0}', '"yellow"')
WHERE id = 1;

这将在 colors 数组的开头插入 "yellow"。

使用 jsonb_delete() 删除字段或数组元素 ​ ​

jsonb_delete() 用于从 JSON 数据中删除指定的字段或数组元素。

sql
UPDATE products
SET details = jsonb_delete(details, '{colors,1}')
WHERE id = 1;

这将删除 colors 数组中的第二个元素（即 "blue"）。

JSON 索引 ​ ​

为了提高查询性能，PostgreSQL 支持对 JSON 数据创建索引。对于 JSONB 数据类型，推荐使用 GIN（广义倒排索引）索引。

示例：为 JSONB 列创建索引 ​ ​

sql
CREATE INDEX idx_products_details ON products USING GIN (details);

小结 ​ ​

PostgreSQL 提供了强大的 JSON 数据类型支持，使得开发者能够灵活地存储、查询和修改 JSON 数据。通过内置的函数和操作符，开发者可以高效地操作 JSON 字段，并结合索引优化查询性能。掌握 JSON 操作将使得开发者能够处理更为复杂和动态的数据结构。

Released under the MIT License.

Copyright © 2024-present PostgreSQL 中文社区