# JSON 操作

URL: https://caijiao.org/postgresql/advanced/advanced-query/json
Source: docs/postgresql/advanced/advanced-query/json.md
Description: 介绍 PostgreSQL 中的 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 操作将使得开发者能够处理更为复杂和动态的数据结构。