如何将JSON数据正确插入PostgreSQL表？Python实现相关疑问

一、修复当前表的插入问题 #

你现有表结构将homeTeam和awayTeam存储为JSON类型，但使用json_populate_recordset时仅插入了id和date，核心原因是参数传递方式不正确。以下是两种可行的修复方案：

方案1：通过JSON字符串传递数据 #

将Python中的JSON对象转为字符串后传入SQL语句：

import psycopg2
import json

# 你的JSON数据
json_data = [
  {
    "id": 1,
    "date": "2023-02-09",
    "homeTeam": {
      "id": "3",
      "players": [
        {
          "id": "5",
          "isInjured": "no",
          "shots": [{"Goal": "yes", "celebrated": True}, {"Goal": "yes", "celebrated": True}]
        }
      ]
    }
  }
]

conn = psycopg2.connect("dbname=你的数据库名 user=你的用户名")
cur = conn.cursor()

cur.execute("""
  INSERT INTO games (id, date, homeTeam)
  SELECT id, date, homeTeam
  FROM json_populate_recordset(NULL::games, %s);
""", (json.dumps(json_data),))

conn.commit()
cur.close()
conn.close()

方案2：使用psycopg2的JSON适配器 #

直接传递Python对象，避免手动字符串转换：

import psycopg2
from psycopg2.extras import Json

# 同上述json_data定义

conn = psycopg2.connect("dbname=你的数据库名 user=你的用户名")
cur = conn.cursor()

cur.execute("""
  INSERT INTO games (id, date, homeTeam)
  SELECT id, date, homeTeam
  FROM json_populate_recordset(NULL::games, %s);
""", (Json(json_data),))

conn.commit()
cur.close()
conn.close()

二、规范化表结构设计（推荐用于复杂查询场景） #

如果需要对球员、射门记录等嵌套数据进行精细化查询或统计，建议将数据拆分为多张关联表，而非存储为JSON：

1. 球队表（teams） #

CREATE TABLE IF NOT EXISTS teams (
  id VARCHAR(50) NOT NULL PRIMARY KEY, -- 对应JSON中homeTeam.id
  -- 可扩展添加球队名称、联赛等字段（若JSON包含）
);

2. 球员表（players） #

CREATE TABLE IF NOT EXISTS players (
  id VARCHAR(50) NOT NULL PRIMARY KEY, -- 对应JSON中players.id
  team_id VARCHAR(50) NOT NULL REFERENCES teams(id),
  is_injured VARCHAR(3) NOT NULL CHECK (is_injured IN ('yes', 'no'))
);

3. 射门记录表（shots） #

CREATE TABLE IF NOT EXISTS shots (
  id SERIAL PRIMARY KEY,
  player_id VARCHAR(50) NOT NULL REFERENCES players(id),
  is_goal VARCHAR(3) NOT NULL CHECK (is_goal IN ('yes', 'no')),
  celebrated BOOLEAN NOT NULL
);

4. 比赛表（games） #

CREATE TABLE IF NOT EXISTS games (
  id INT NOT NULL PRIMARY KEY,
  date DATE NOT NULL,
  home_team_id VARCHAR(50) NOT NULL REFERENCES teams(id),
  away_team_id VARCHAR(50) REFERENCES teams(id) -- 若JSON包含awayTeam字段
);

从JSON批量插入到规范化表 #

通过PostgreSQL的JSON函数逐层解析嵌套数据，结合INSERT...SELECT实现批量插入：

插入球队

INSERT INTO teams (id)
SELECT DISTINCT (homeTeam->>'id')::VARCHAR(50)
FROM json_populate_recordset(NULL::games, %s)
ON CONFLICT (id) DO NOTHING; -- 避免重复插入

插入球员

INSERT INTO players (id, team_id, is_injured)
SELECT 
  player->>'id' AS player_id,
  homeTeam->>'id' AS team_id,
  player->>'isInjured' AS is_injured
FROM json_populate_recordset(NULL::games, %s) g,
     json_array_elements(g.homeTeam->'players') AS player
ON CONFLICT (id) DO NOTHING;

插入射门记录

INSERT INTO shots (player_id, is_goal, celebrated)
SELECT 
  player->>'id' AS player_id,
  shot->>'Goal' AS is_goal,
  (shot->>'celebrated')::BOOLEAN AS celebrated
FROM json_populate_recordset(NULL::games, %s) g,
     json_array_elements(g.homeTeam->'players') AS player,
     json_array_elements(player->'shots') AS shot;

插入比赛

INSERT INTO games (id, date, home_team_id)
SELECT 
  id,
  date,
  (homeTeam->>'id')::VARCHAR(50) AS home_team_id
FROM json_populate_recordset(NULL::games, %s)
ON CONFLICT (id) DO NOTHING;

Python执行时，同样使用上述两种参数传递方式（JSON字符串或Json适配器）即可。

三、SELECT语句插入数据的核心逻辑 #

无论采用哪种表结构，核心都是通过PostgreSQL的JSON函数解析数据，再用INSERT...SELECT完成插入：

• 平级JSON字段：直接用json_populate_recordset映射到表列
• 嵌套JSON数组：用json_array_elements展开数组，逐层提取字段
• 重复数据处理：通过ON CONFLICT (...) DO NOTHING或ON CONFLICT (...) DO UPDATE避免冗余

内容的提问来源于stack exchange，提问作者asap_montay