SQL如何读取JSON文件:实用指南与代码示例
在现代数据应用中,JSON(JavaScript Object Notation)因其轻量级、易读性和灵活的结构,成为数据交换的主流格式之一,而SQL作为关系型数据库的标准查询语言,如何高效读取JSON文件中的数据,成为许多开发者面临的问题,本文将详细介绍SQL读取JSON文件的多种方法,包括直接读取、借助数据库函数、以及通过ETL工具等场景,并提供具体代码示例。
SQL直接读取JSON文件?——先明确前提
首先需要明确:原生SQL语句本身并不直接支持读取本地文件系统中的JSON文件,SQL的核心操作对象是数据库表(或视图),而非本地文件系统。“读取JSON文件”通常需要结合以下场景之一:
- 数据库内置JSON函数:若JSON文件已导入数据库(如存储为表的字段、JSON列或外部表),可通过SQL的JSON函数解析数据;
- 数据库外部表功能:部分数据库(如Oracle、PostgreSQL、Snowflake)支持通过外部表直接映射JSON文件路径,实现“类直接读取”;
- ETL/ELT工具:通过工具(如Apache NiFi、Talend、dbt)将JSON文件加载到数据库,再用SQL查询;
- 编程接口结合:通过Python、Java等语言读取JSON文件并写入临时表,再用SQL操作。
以下将聚焦数据库内置JSON函数和外部表两种最常用的SQL原生方法。
方法1:将JSON文件导入数据库,再用SQL解析函数
这是最常见的方式:先将JSON文件加载到数据库中(存储为表的文本字段、JSON列或专用JSON类型列),再通过SQL的JSON解析函数提取数据,不同数据库的JSON函数语法略有差异,以下以主流数据库为例说明。
场景1:JSON文件存储为表的文本字段(如VARCHAR/TEXT类型)
假设JSON文件内容如下(data.json):
[
{"id": 1, "name": "Alice", "age": 25, "contacts": {"email": "alice@example.com", "phone": "123456"}},
{"id": 2, "name": "Bob", "age": 30, "contacts": {"email": "bob@example.com", "phone": "789012"}},
{"id": 3, "name": "Charlie", "age": 35, "hobbies": ["reading", "swimming"]}
]
将其导入数据库表(如json_data,json_content字段为TEXT类型存储JSON字符串):
MySQL/MariaDB:使用JSON函数解析
-- 创建表并导入数据(假设已通过LOAD DATA或客户端工具导入json_content字段)
CREATE TABLE json_data (
id INT AUTO_INCREMENT PRIMARY KEY,
json_content TEXT
);
-- 插入示例数据(实际中可能从文件批量导入)
INSERT INTO json_data (json_content) VALUES
('[{"id": 1, "name": "Alice", "age": 25, "contacts": {"email": "alice@example.com", "phone": "123456"}}, {"id": 2, "name": "Bob", "age": 30, "contacts": {"email": "bob@example.com", "phone": "789012"}}, {"id": 3, "name": "Charlie", "age": 35, "hobbies": ["reading", "swimming"]}]');
-- 解析JSON数组中的每个对象(需结合JSON_TABLE函数,MySQL 8.0+支持)
SELECT
obj.id,
obj.name,
obj.age,
contacts.email AS email,
contacts.phone AS phone,
hobbies.value AS hobby
FROM json_data,
JSON_TABLE(
json_content,
'$[*]' COLUMNS( -- '$[*]'表示解析JSON数组中的所有对象
id INT PATH '$.id',
name VARCHAR(50) PATH '$.name',
age INT PATH '$.age',
contacts VARCHAR(100) PATH '$.contacts' -- 可进一步嵌套解析
) AS obj
) AS obj
-- 解嵌套的contacts对象(JSON_TABLE支持多层嵌套)
LEFT JOIN JSON_TABLE(
obj.contacts,
'$' COLUMNS(
email VARCHAR(100) PATH '$.email',
phone VARCHAR(20) PATH '$.phone'
)
) AS contacts ON TRUE
-- 解嵌套的hobbies数组(JSON数组需用$[*]或NESTED PATH)
LEFT JOIN JSON_TABLE(
(SELECT JSON_CONTENT FROM json_data WHERE JSON_CONTAINS(json_content, obj.id, '$.id')),
'$.hobbies[*]' COLUMNS(
hobby VARCHAR(50) PATH '$'
)
) AS hobbies ON TRUE;
PostgreSQL:使用jsonb类型与jsonb_*函数
PostgreSQL对JSON支持更友好,推荐使用jsonb类型(二进制JSON,查询效率更高):
-- 创建表并导入数据(jsonb字段自动解析JSON)
CREATE TABLE json_data (
id SERIAL PRIMARY KEY,
json_content JSONB
);
-- 插入数据(假设已从文件导入)
INSERT INTO json_data (json_content) VALUES
('[{"id": 1, "name": "Alice", "age": 25, "contacts": {"email": "alice@example.com", "phone": "123456"}}, {"id": 2, "name": "Bob", "age": 30, "contacts": {"email": "bob@example.com", "phone": "789012"}}, {"id": 3, "name": "Charlie", "age": 35, "hobbies": ["reading", "swimming"]}]');
-- 方式1:使用jsonb_array_elements + jsonb_each_text 解析数组+对象
WITH expanded_data AS (
SELECT
elem->>'id' AS id,
elem->>'name' AS name,
elem->>'age' AS age,
elem->'contacts' AS contacts_jsonb -- 提取contacts对象
FROM json_data, jsonb_array_elements(json_content) AS elem
)
SELECT
id::INT,
name,
age::INT,
contacts_jsonb->>'email' AS email,
contacts_jsonb->>'phone' AS phone,
hobby
FROM expanded_data
LEFT JOIN LATERAL jsonb_array_elements_text(expanded_data.contacts_jsonb->'hobbies') AS hobby ON TRUE; -- hobbies数组需单独处理(注意:原JSON中hobbies是顶级字段,需调整逻辑)
-- 方式2:使用jsonb_to_recordset(更简洁,适用于数组对象)
SELECT
(data->>'id')::INT AS id,
(data->>'name') AS name,
(data->>'age')::INT AS age,
(data->'contacts'->>'email') AS email,
(data->'contacts'->>'phone') AS phone
FROM json_data, jsonb_to_recordset(json_content) AS data(
id INT,
name VARCHAR(50),
age INT,
contacts JSONB
);
SQL Server:使用OPENJSON函数
SQL Server 2016+内置OPENJSON函数,需先启用OPTION (JSON_ENABLE_ARRAY_WRAPPER):
-- 创建表并导入数据
CREATE TABLE json_data (
id INT IDENTITY PRIMARY KEY,
json_content NVARCHAR(MAX)
);
INSERT INTO json_data (json_content) VALUES
(N'[{"id": 1, "name": "Alice", "age": 25, "contacts": {"email": "alice@example.com", "phone": "123456"}}, {"id": 2, "name": "Bob", "age": 30, "contacts": {"email": "bob@example.com", "phone": "789012"}}, {"id": 3, "name": "Charlie", "age": 35, "hobbies": ["reading", "swimming"]}]');
-- 使用OPENJSON解析JSON数组
SELECT
obj.id,
obj.name,
obj.age,
contacts.email,
contacts.phone,
hobby.value AS hobby
FROM json_data
CROSS APPLY OPENJSON(json_content) WITH ( -- 解析数组中的对象
id INT '$.id',
name VARCHAR(50) '$.name',
age INT '$.age',
contacts NVARCHAR(MAX) '$.contacts' AS json -- contacts字段保留为JSON
) AS obj
-- 解嵌套的contacts对象
CROSS APPLY OPENJSON(obj.contacts) WITH (
email VARCHAR(100) '$.email',
phone VARCHAR(20) '$.phone'
) AS contacts
-- 解嵌套的hobbies数组(需从原始JSON中单独提取)
CROSS APPLY OPENJSON((SELECT json_content FROM json_data WHERE id = obj.id), '$.hobbies') hobby;
场景2:JSON文件存储为专用JSON列(如MySQL的JSON、PostgreSQL的JSONB)
若数据库支持原生JSON类型(如MySQL 5.7+的JSON、PostgreSQL的jsonb),可直接导入JSON文件并利用类型优势:
-- MySQL示例:导入JSON



还没有评论,来说两句吧...