MariaDB JSON_TABLE() 函数的基础用法与实例

MariaDB 的 JSON_TABLE() 函数用来将 JSON 数据转换为结构化的表格格式。

发布于

在处理 JSON 数据时，我们经常需要将 JSON 数据转换为结构化的表格格式，以便进行更复杂的查询和分析。MariaDB 的 JSON_TABLE() 函数正是为了满足这一需求而设计的。本文将介绍 JSON_TABLE() 函数的基本用法，并通过具体实例来展示如何在实际应用中使用这个函数。

语法

JSON_TABLE() 函数的基本语法如下：

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
  • expr 是一个 JSON 格式的字符串或者列。
  • path 是一个 JSON 路径表达式，指定 expr 中的 JSON 数据的位置。
  • column_list 是一个或多个列定义，指定如何将 JSON 数据转换为表格中的列。
  • alias 是结果表格的别名。

实例

将 JSON 对象转换为单行表格

SET @json = '{"name": "Alice", "age": 25}';
SELECT * FROM JSON_TABLE(@json, '$' COLUMNS (name VARCHAR(100) PATH '$.name', age INT PATH '$.age')) AS jt;

输出结果为：

+-------+------+
| name  | age  |
+-------+------+
| Alice |   25 |
+-------+------+

将 JSON 数组转换为多行表格

SET @json = '["apple", "banana", "cherry"]';
SELECT * FROM JSON_TABLE(@json, '$[*]' COLUMNS (fruit VARCHAR(100) PATH '$')) AS jt;

输出结果为：

+--------+
| fruit  |
+--------+
| apple  |
| banana |
| cherry |
+--------+

处理嵌套的 JSON 对象

SET @json = '{"person": {"name": "Alice", "age": 25}, "city": "Paris"}';
SELECT * FROM JSON_TABLE(@json, '$' COLUMNS (name VARCHAR(100) PATH '$.person.name', age INT PATH '$.person.age', city VARCHAR(100) PATH '$.city')) AS jt;

输出结果为：

+-------+------+-------+
| name  | age  | city  |
+-------+------+-------+
| Alice |   25 | Paris |
+-------+------+-------+

从表中查询并转换 JSON 数据

首先，我们创建一个表并插入数据。

CREATE TABLE users (id INT, profile JSON);
INSERT INTO users VALUES (1, '{"name": "Alice", "age": 25}');

然后，我们可以将表中的 JSON 数据转换为表格。

SELECT jt.* FROM users, JSON_TABLE(profile, '$' COLUMNS (name VARCHAR(100) PATH '$.name', age INT PATH '$.age')) AS jt;

输出结果为：

+-------+------+
| name  | age  |
+-------+------+
| Alice |   25 |
+-------+------+

处理复杂的 JSON 数据

SET @json = '[{"name": "Alice", "skills": ["SQL", "Java"]}, {"name": "Bob", "skills": ["Python"]}]';
SELECT * FROM JSON_TABLE(@json, '$[*]' COLUMNS (name VARCHAR(100) PATH '$.name', skill VARCHAR(100) PATH '$.skills[0]')) AS jt;

输出结果为：

+-------+--------+
| name  | skill  |
+-------+--------+
| Alice | SQL    |
| Bob   | Python |
+-------+--------+

相关函数

结论

JSON_TABLE() 函数是处理 JSON 数据时非常有用的工具，它允许我们将 JSON 数据转换为结构化的表格格式。通过上述实例，我们可以看到它在实际应用中的灵活性和便捷性。无论是简单的数据转换，还是复杂的 JSON 结构处理，JSON_TABLE() 都能提供有效的解决方案。在处理 JSON 数据时，它是一个不可或缺的函数。