Sql怎么读取json文件

SQL如何读取JSON文件：实用指南与代码示例

在现代数据应用中,JSON（JavaScript Object Notation）因其轻量级、易读性和灵活的结构，成为数据交换的主流格式之一，而SQL作为关系型数据库的标准查询语言，如何高效读取JSON文件中的数据，成为许多开发者面临的问题，本文将详细介绍SQL读取JSON文件的多种方法，包括直接读取、借助数据库函数、以及通过ETL工具等场景，并提供具体代码示例。

SQL直接读取JSON文件？——先明确前提

首先需要明确：原生SQL语句本身并不直接支持读取本地文件系统中的JSON文件，SQL的核心操作对象是数据库表（或视图），而非本地文件系统。“读取JSON文件”通常需要结合以下场景之一：

1. 数据库内置JSON函数：若JSON文件已导入数据库（如存储为表的字段、JSON列或外部表），可通过SQL的JSON函数解析数据；
2. 数据库外部表功能：部分数据库（如Oracle、PostgreSQL、Snowflake）支持通过外部表直接映射JSON文件路径，实现“类直接读取”；
3. ETL/ELT工具：通过工具（如Apache NiFi、Talend、dbt）将JSON文件加载到数据库，再用SQL查询；
4. 编程接口结合：通过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