MySQL函数：`JSON_TABLE()`将 JSON 数据转换为关系型表，实现数据扁平化。

JSON_TABLE()：MySQL 中 JSON 数据扁平化的利器

大家好，今天我们要深入探讨 MySQL 中一个非常强大的函数：JSON_TABLE()。在现代应用开发中，JSON 作为一种轻量级的数据交换格式被广泛使用。然而，在数据库中，我们通常需要以关系型的方式存储和处理数据。JSON_TABLE() 函数正是连接 JSON 数据和关系型世界的桥梁，它允许我们将 JSON 数据转换为关系型表，从而方便我们进行查询、分析和操作。

1. JSON 数据的挑战与解决方案

JSON 数据以键值对的形式组织，可以嵌套复杂的结构，例如数组和对象。这种灵活性使得 JSON 非常适合表示各种类型的数据。但是，在关系型数据库中，我们需要将数据存储在行和列中，这与 JSON 的结构化方式不同。直接操作 JSON 数据通常比较困难，效率也较低。

例如，我们有一个包含用户信息的 JSON 数组：

[
  {
    "id": 1,
    "name": "Alice",
    "age": 30,
    "address": {
      "city": "New York",
      "country": "USA"
    },
    "hobbies": ["reading", "hiking"]
  },
  {
    "id": 2,
    "name": "Bob",
    "age": 25,
    "address": {
      "city": "London",
      "country": "UK"
    },
    "hobbies": ["coding", "gaming"]
  }
]

如果我们想查询所有居住在 "New York" 的用户，或者找到所有喜欢 "reading" 的用户，直接在 JSON 字符串上操作会非常复杂。我们需要解析 JSON，遍历数组，然后提取所需的信息。

JSON_TABLE() 函数提供了一种更有效的方式来解决这个问题。它可以将 JSON 数组转换为一个虚拟的表，每一行代表 JSON 数组中的一个元素，每一列代表 JSON 对象中的一个属性。这样，我们就可以使用标准的 SQL 查询来操作 JSON 数据。

2. JSON_TABLE() 函数的语法

JSON_TABLE() 函数的语法如下：

JSON_TABLE(
  json_doc,
  path COLUMNS (
    column_name data_type PATH json_path [ERROR ON EMPTY | DEFAULT value]
    [, ...]
  )
) AS alias

让我们逐一解释这些参数：

• json_doc: 包含 JSON 数据的表达式。这可以是一个 JSON 字符串，一个存储 JSON 数据的列，或者一个返回 JSON 数据的函数。
• path: 一个 JSON Path 表达式，用于指定要从 JSON 文档中提取的数组或对象。如果 json_doc 是一个 JSON 数组，path 通常是 '$[*]'，表示数组中的所有元素。
• COLUMNS: 定义虚拟表的列。对于每一列，我们需要指定：
  • column_name: 列的名称。
  • data_type: 列的数据类型 (例如 INT, VARCHAR, DATE)。
  • PATH json_path: 一个 JSON Path 表达式，用于指定从 JSON 文档中提取该列的值的路径。
  • ERROR ON EMPTY | DEFAULT value: 用于处理当 JSON Path 不存在或为空时的情况。
    • ERROR ON EMPTY: 如果 JSON Path 不存在或为空，则抛出一个错误。
    • DEFAULT value: 如果 JSON Path 不存在或为空，则使用指定的默认值。
• alias: 虚拟表的别名。

3. JSON_TABLE() 函数的使用示例

让我们回到之前的用户 JSON 数据，并使用 JSON_TABLE() 函数将其转换为一个虚拟表。

首先，我们需要将 JSON 数据存储在一个变量中：

SET @json_data = '[
  {
    "id": 1,
    "name": "Alice",
    "age": 30,
    "address": {
      "city": "New York",
      "country": "USA"
    },
    "hobbies": ["reading", "hiking"]
  },
  {
    "id": 2,
    "name": "Bob",
    "age": 25,
    "address": {
      "city": "London",
      "country": "UK"
    },
    "hobbies": ["coding", "gaming"]
  }
]';

现在，我们可以使用 JSON_TABLE() 函数来创建一个虚拟表：

SELECT
  jt.id,
  jt.name,
  jt.age,
  jt.city,
  jt.country,
  jt.hobbies
FROM
  JSON_TABLE(
    @json_data,
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      name VARCHAR(255) PATH '$.name',
      age INT PATH '$.age',
      city VARCHAR(255) PATH '$.address.city',
      country VARCHAR(255) PATH '$.address.country',
      hobbies JSON PATH '$.hobbies'
    )
  ) AS jt;

这个查询将返回以下结果：

id	name	age	city	country	hobbies
1	Alice	30	New York	USA	["reading", "hiking"]
2	Bob	25	London	UK	["coding", "gaming"]

在这个例子中，我们使用了 '$[*]' 作为 path，表示我们想要迭代 JSON 数组中的所有元素。对于每一列，我们使用 JSON Path 表达式来指定要从 JSON 对象中提取的值。例如，'$.id' 表示从 JSON 对象中提取 id 属性的值，'$.address.city' 表示从 address 对象的 city 属性中提取值。

现在，我们可以使用标准的 SQL 查询来操作这个虚拟表。例如，要查询所有居住在 "New York" 的用户，我们可以使用以下查询：

SELECT
  jt.id,
  jt.name
FROM
  JSON_TABLE(
    @json_data,
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      name VARCHAR(255) PATH '$.name',
      city VARCHAR(255) PATH '$.address.city'
    )
  ) AS jt
WHERE
  jt.city = 'New York';

这个查询将返回以下结果：

id	name
1	Alice

4. 处理缺失值和默认值

当 JSON Path 不存在或为空时，JSON_TABLE() 函数的行为取决于我们是否指定了 ERROR ON EMPTY 或 DEFAULT value。

如果没有指定任何选项，默认情况下，JSON_TABLE() 函数将返回 NULL。例如，如果我们的 JSON 数据中缺少 age 属性：

SET @json_data = '[
  {
    "id": 1,
    "name": "Alice",
    "address": {
      "city": "New York",
      "country": "USA"
    }
  },
  {
    "id": 2,
    "name": "Bob",
    "age": 25,
    "address": {
      "city": "London",
      "country": "UK"
    }
  }
]';

然后我们执行以下查询：

SELECT
  jt.id,
  jt.name,
  jt.age
FROM
  JSON_TABLE(
    @json_data,
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      name VARCHAR(255) PATH '$.name',
      age INT PATH '$.age'
    )
  ) AS jt;

这个查询将返回以下结果：

id	name	age
1	Alice	NULL
2	Bob	25

我们可以使用 DEFAULT 子句来指定一个默认值，以替代 NULL。例如，我们可以将 age 的默认值设置为 0：

SELECT
  jt.id,
  jt.name,
  jt.age
FROM
  JSON_TABLE(
    @json_data,
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      name VARCHAR(255) PATH '$.name',
      age INT PATH '$.age' DEFAULT 0
    )
  ) AS jt;

这个查询将返回以下结果：

id	name	age
1	Alice	0
2	Bob	25

如果我们指定了 ERROR ON EMPTY，则当 JSON Path 不存在或为空时，JSON_TABLE() 函数将抛出一个错误。例如：

SELECT
  jt.id,
  jt.name,
  jt.age
FROM
  JSON_TABLE(
    @json_data,
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      name VARCHAR(255) PATH '$.name',
      age INT PATH '$.age' ERROR ON EMPTY
    )
  ) AS jt;

在这个例子中，由于第一个 JSON 对象缺少 age 属性，JSON_TABLE() 函数将抛出一个错误。

5. 处理嵌套的 JSON 数据

JSON_TABLE() 函数可以处理嵌套的 JSON 数据。我们可以使用 JSON Path 表达式来访问嵌套对象中的属性。例如，我们可以提取用户的城市和国家：

SELECT
  jt.id,
  jt.name,
  jt.city,
  jt.country
FROM
  JSON_TABLE(
    @json_data,
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      name VARCHAR(255) PATH '$.name',
      city VARCHAR(255) PATH '$.address.city',
      country VARCHAR(255) PATH '$.address.country'
    )
  ) AS jt;

这个查询将返回以下结果：

id	name	city	country
1	Alice	New York	USA
2	Bob	London	UK

6. 处理 JSON 数组

JSON_TABLE() 函数还可以处理 JSON 数组。我们可以使用 JSON_ARRAYAGG() 函数将一个数组转换为一个字符串，然后使用 JSON_TABLE() 函数将其转换为一个虚拟表。

例如，假设我们有一个包含产品信息的 JSON 数组，每个产品都有一个 tags 属性，它是一个包含标签的数组：

[
  {
    "id": 1,
    "name": "Product A",
    "tags": ["tag1", "tag2"]
  },
  {
    "id": 2,
    "name": "Product B",
    "tags": ["tag2", "tag3", "tag4"]
  }
]

我们可以使用以下查询来将这个 JSON 数组转换为一个虚拟表，其中每一行代表一个标签：

SELECT
  p.id,
  p.name,
  t.tag
FROM
  JSON_TABLE(
    @json_data,
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      name VARCHAR(255) PATH '$.name',
      tags JSON PATH '$.tags'
    )
  ) AS p,
  JSON_TABLE(
    p.tags,
    '$[*]' COLUMNS (
      tag VARCHAR(255) PATH '$'
    )
  ) AS t;

这个查询将返回以下结果：

id	name	tag
1	Product A	tag1
1	Product A	tag2
2	Product B	tag2
2	Product B	tag3
2	Product B	tag4

在这个例子中，我们使用了两个 JSON_TABLE() 函数。第一个 JSON_TABLE() 函数用于将产品 JSON 数组转换为一个虚拟表，其中包含产品的 id、name 和 tags 属性。第二个 JSON_TABLE() 函数用于将 tags 数组转换为一个虚拟表，其中包含标签的值。我们使用逗号连接两个 JSON_TABLE() 函数，以便将产品和标签连接在一起。

7. 性能考虑

JSON_TABLE() 函数是一个强大的工具，但它也可能影响性能。将 JSON 数据转换为关系型表需要一定的计算开销。因此，在使用 JSON_TABLE() 函数时，我们需要考虑以下几点：

• 避免在大型 JSON 数据上使用 JSON_TABLE() 函数。 如果 JSON 数据非常大，转换为关系型表可能会非常耗时。在这种情况下，可以考虑使用其他方法来处理 JSON 数据，例如使用专门的 JSON 处理库。
• 尽量减少 JSON_TABLE() 函数的使用次数。 如果需要在多个查询中使用相同的 JSON 数据，可以将 JSON 数据转换为一个临时表，然后在多个查询中使用该临时表。
• 优化 JSON Path 表达式。 JSON Path 表达式的效率会影响 JSON_TABLE() 函数的性能。尽量使用简单的 JSON Path 表达式，避免使用复杂的表达式。

8. 实际应用案例

JSON_TABLE() 函数在实际应用中有很多用途。以下是一些常见的应用案例：

• 数据分析。 我们可以使用 JSON_TABLE() 函数将存储在 JSON 数据中的数据转换为关系型表，然后使用 SQL 查询来分析数据。
• 数据集成。 我们可以使用 JSON_TABLE() 函数将来自不同来源的 JSON 数据集成到同一个数据库中。
• API 开发。 我们可以使用 JSON_TABLE() 函数将 API 返回的 JSON 数据转换为关系型表，然后使用 SQL 查询来过滤和排序数据，最后将结果返回给客户端。
• 日志分析。 许多应用程序以 JSON 格式记录日志。JSON_TABLE() 可以帮助我们将这些日志数据转换为关系型表，从而方便我们进行分析和监控。

9. 其他 JSON 函数的配合使用

JSON_TABLE() 函数通常与其他 JSON 函数一起使用，以实现更复杂的数据处理。例如：

• JSON_EXTRACT(): 用于从 JSON 文档中提取特定的值。
• JSON_ARRAYAGG(): 用于将多个 JSON 值聚合到一个 JSON 数组中。
• JSON_OBJECTAGG(): 用于将多个键值对聚合到一个 JSON 对象中。
• JSON_CONTAINS(): 用于检查 JSON 文档是否包含特定的值。
• JSON_SEARCH(): 用于在 JSON 文档中搜索特定的值。

通过将 JSON_TABLE() 函数与其他 JSON 函数结合使用，我们可以实现各种复杂的数据处理任务。

10. 总结：JSON_TABLE() 是 JSON 数据处理的强大工具

JSON_TABLE() 函数是 MySQL 中一个非常强大的工具，它可以将 JSON 数据转换为关系型表，从而方便我们进行查询、分析和操作。通过灵活的 JSON Path 表达式和错误处理机制，JSON_TABLE() 函数可以处理各种复杂的 JSON 数据结构。虽然在使用 JSON_TABLE() 函数时需要考虑性能因素，但在许多实际应用中，它仍然是一个非常有用的工具。掌握 JSON_TABLE() 函数可以极大地提高我们处理 JSON 数据的效率和灵活性。