逐行读取部分JSON文档时PostgreSQL报错求助

问题背景 #

我尝试将Kaggle上的Jeopardy JSON数据导入PostgreSQL。原始数据为JSON文档数组，通过Python转换为每行一条JSON的文件（测试取前50条），代码如下：

import json

f = open('200k_questions.json')
data = json.load(f) # 字典数组
file = open('temp_ut.json','w') # 打开文件准备写入
max=50
for i in range(0,max):
    temp = data[i]
    json.dump(temp, file)
    file.write('\n')
file.close()
f.close()

生成的文件前4行示例：

{"category": "HISTORY", "air_date": "2004-12-31", "question": "'For the last 8 years of his life, Galileo was under house arrest for espousing this man's theory'", "value": "$200", "answer": "Copernicus", "round": "Jeopardy!", "show_number": "4680"}
{"category": "ESPN's TOP 10 ALL-TIME ATHLETES", "air_date": "2004-12-31", "question": "'No. 2: 1912 Olympian; football star at Carlisle Indian School; 6 MLB seasons with the Reds, Giants & Braves'", "value": "$200", "answer": "Jim Thorpe", "round": "Jeopardy!", "show_number": "4680"}
{"category": "EVERYBODY TALKS ABOUT IT...", "air_date": "2004-12-31", "question": "'The city of Yuma in this state has a record average of 4,055 hours of sunshine each year'", "value": "$200", "answer": "Arizona", "round": "Jeopardy!", "show_number": "4680"}
{"category": "THE COMPANY LINE", "air_date": "2004-12-31", "question": "'In 1963, live on \"The Art Linkletter Show\", this company served its billionth burger'", "value": "$200", "answer": "McDonald\\'s", "round": "Jeopardy!", "show_number": "4680"}

已在PostgreSQL成功执行建库建表命令：

CREATE DATABASE jeopardy;
CREATE TABLE questions(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, question jsonb NOT NULL);

执行导入命令时：

\COPY questions (question) FROM 'C:\Users\malawley\Documents\Northwestern MS\Course 6 MSDS 420 Database\TermPaper\data\jeopardy\temp_ut.json';

出现报错：

ERROR:  invalid input syntax for type json
DETAIL:  Token "The" is invalid.
CONTEXT:  JSON data, line 1: ..."2004-12-31", "question": "'In 1963, live on "The...
COPY questions, line 4, column question: "{"category": "THE COMPANY LINE", "air_date": "2004-12-31", "question": "'In 1963, live on "The Art L..."

问题集中在部分JSON文档，例如：

{"category": "THE COMPANY LINE", "air_date": "2004-12-31", "question": "'In 1963, live on \"The Art Linkletter Show\", this company served its billionth burger'", "value": "$200", "answer": "McDonald\'s", "round": "Jeopardy!", "show_number": "4680"}
{"category": "EPITAPHS & TRIBUTES", "air_date": "2004-12-31", "question": "'\"And away we go\"'", "value": "$400", "answer": "Jackie Gleason", "round": "Jeopardy!", "show_number": "4680"}
{"category": "ESPN's TOP 10 ALL-TIME ATHLETES", "air_date": "2004-12-31", "question": "'No. 1: Lettered in hoops, football & lacrosse at Syracuse & if you think he couldn't act, ask his 11 \"unclean\" buddies'", "value": "$600", "answer": "Jim Brown", "round": "Jeopardy!", "show_number": "4680"}

用以下Python函数验证全部200k条文档均为合法JSON，但PostgreSQL仍报错：

def is_json(myjson):
  try:
    json.loads(myjson)
  except ValueError as e:
    return False
  return True

问题原因 #

PostgreSQL通过\COPY读取文件时，会先做一层文本转义处理：单个反斜杠会被当成转义符消耗掉。而Python的json.dump输出的JSON字符串中，双引号用单个反斜杠转义（\"），PostgreSQL读取后会把\"解析为未转义的双引号，直接结束当前JSON字符串，导致后续内容成为无效token，触发语法错误。

Python的json.loads能正常解析，是因为它遵循标准JSON规范：字符串内的双引号用单个反斜杠转义即可，不会提前消耗反斜杠。

解决方案 #

方案1：修改Python导出代码，增强转义 #

在导出时将单个反斜杠替换为两个，确保PostgreSQL解析时能识别为JSON转义符：

import json

f = open('200k_questions.json', encoding='utf-8')
data = json.load(f)
file = open('temp_ut.json', 'w', encoding='utf-8')
max_count = 50
for i in range(max_count):
    temp = data[i]
    # 先转成JSON字符串，再替换反斜杠
    json_str = json.dumps(temp, ensure_ascii=False)
    json_str = json_str.replace('\\', '\\\\')
    file.write(json_str + '\n')
file.close()
f.close()

方案2：用临时表预处理数据 #

先将数据导入为文本类型，处理转义后再转为jsonb：

-- 创建临时表存储原始文本
CREATE TABLE temp_questions(raw_text text);
-- 导入数据
\COPY temp_questions (raw_text) FROM 'C:\Users\malawley\Documents\Northwestern MS\Course 6 MSDS 420 Database\TermPaper\data\jeopardy\temp_ut.json';
-- 处理转义并插入目标表
INSERT INTO questions(question)
SELECT replace(raw_text, '\\"', '\\\\\"')::jsonb FROM temp_questions;
-- 清理临时表
DROP TABLE temp_questions;

方案3：使用CSV格式导入 #

指定CSV格式并设置特殊分隔符/引号，避免PostgreSQL提前解析转义字符：

\COPY questions (question) FROM 'C:\Users\malawley\Documents\Northwestern MS\Course 6 MSDS 420 Database\TermPaper\data\jeopardy\temp_ut.json' FORMAT csv QUOTE E'\x00' DELIMITER E'\x01';

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