如何在SQL数据库中组织存储嵌套JSON对象数据

以下是两种可直接落地的实现方式，你可以根据自身需求和学习进度选择：

方案1：新手友好的极简实现（原生JSON字段存储） #

目前主流关系型数据库（MySQL 5.7+、PostgreSQL、SQLite 3.9+）都支持原生JSON类型，不需要拆分嵌套结构，直接对应JSON结构建表即可，10分钟就能完成搭建：

示例建表语句（MySQL） #

CREATE TABLE `api_data` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
  `server` varchar(255) NOT NULL COMMENT '对应meta.server',
  `user` varchar(255) NOT NULL COMMENT '对应meta.user',
  `response_status` varchar(10) NOT NULL COMMENT '对应meta.response',
  `tags` JSON NOT NULL COMMENT '对应data.tags数组',
  `parameters` JSON NOT NULL COMMENT '对应data.parameters数组',
  `campaign_info` JSON NOT NULL COMMENT '对应data.responses.campaign对象',
  `publisher_info` JSON NOT NULL COMMENT '对应data.responses.publisher对象',
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

如果不需要频繁单独查询meta里的字段，甚至可以简化为只存两个JSON字段：

CREATE TABLE `api_data` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `meta` JSON NOT NULL,
  `data` JSON NOT NULL,
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心优势 #

• 零结构拆解成本，完全匹配原有JSON格式，新手不需要额外学习表关联知识
• 支持直接查询JSON内部字段，以MySQL为例：

  -- 查询发布状态为激活的活动
  SELECT * FROM api_data 
  WHERE campaign_info->>"$.content.active" = true;
  

适合入门阶段使用，或者业务需求以存储为主、不需要复杂关联统计的场景。

方案2：规范关系型数据库设计（拆表存储） #

如果后续需要做复杂数据分析、对查询性能要求高，可以把嵌套结构拆解为独立表，用外键关联，你提供的JSON可以拆为以下6张表：

• meta表：存储接口元信息
  • id：主键
  • server：varchar
  • user：varchar
  • response_status：varchar
  • data_id：外键关联data表主键
• data表：存储data层的关联关系
  • id：主键
• tags表：存储标签数组，多对一关联data表
  • id：主键
  • data_id：外键关联data表
  • tag_value：varchar，存储单个标签内容
• parameters表：存储参数数组，多对一关联data表
  • id：主键
  • data_id：外键关联data表
  • ref_value：varchar，存储$ref字段内容
• campaign表：存储活动信息
  • id：主键
  • data_id：外键关联data表
  • name：varchar
  • description：text
  • content_id：varchar
  • publisher：varchar
  • active：tinyint(1)（布尔类型）
  • launched_timestamp：bigint（时间戳）
• campaign_countries表：存储活动对应的国家用户数据，多对一关联campaign表
  • campaign_id：外键关联campaign表
  • country_code：varchar，如USA
  • user_count：bigint，如753365
  • 联合主键（campaign_id, country_code）
• publisher表：存储发布方信息
  • id：主键
  • data_id：外键关联data表
  • name：varchar
  • outer_id：varchar，对应原publisher.id字段
  • authorized：tinyint(1)
  • default_currency：varchar

核心优势 #

• 数据完全结构化，查询效率远高于JSON字段，支持复杂关联统计
• 数据一致性更高，方便做字段校验、索引优化
  适合有一定数据库基础、业务需求复杂的场景。

选型建议 #

刚入门优先选方案1，完全满足常规使用需求，学习成本极低；后续熟练掌握表关联、索引等知识后，再根据业务需要切换为方案2即可。

内容的提问来源于stack exchange，提问作者Marú Segovia