如何将JSONL文件安全导入Postgres 10数据库并按字段筛选记录

现有表结构 #

Table "musicbrainz.acoustid_meta"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 id           | integer           |           | not null |
 track        | character varying |           |          |
 artist       | character varying |           |          |
 album        | character varying |           |          |
 album_artist | character varying |           |          |
 track_no     | character varying |           |          |
 disc_no      | character varying |           |          |
 year         | character varying |           |          |
Indexes:
    "acoustid_meta_index" btree (id)

原CSV导入逻辑 #

CSV样例 #

id,track,artist,album,album_artist,track_no,disc_no,year
23033007,Satellite,Dave Matthews Band,Under the Table & Dreaming,Dave Matthews Band,3,\N,1994

导入命令 #

psql jthinksearch -c "copy musicbrainz.acoustid_meta from '/home/ubuntu/code/acoustid-server/meta.full.$LATEST.csv' DELIMITER ',' CSV HEADER";

需求说明 #

当前数据源已改为JSONL格式，单行结构如下：

{"id":339058430,"track":"Track14","artist":"Unknown Artist","album":"Unknown Title","album_artist":"Unknown Artist","track_no":14,"disc_no":null,"year":null,"created":"2020-02-01T00:00:13.225963+00:00"}

JSONL行规则：

• 包含"created":"[时间戳]"字段代表需要新增的记录
• 包含"updated":"[时间戳]"字段代表需要替换的已有记录

此前尝试用sed转CSV导入效果差，且5张表单独写sed脚本效率低，需要实现用cross join jsonb_populate_record插入时，筛选仅含created字段的记录。 #

实现方案 #

核心筛选逻辑 #

PostgreSQL原生支持JSON字段键存在性判断，直接在WHERE子句中加对应判断条件即可，无需转换为CSV。

完整操作步骤 #

1. 先将JSONL原始数据导入临时表

-- 创建临时表存储JSONL原始内容
CREATE TEMP TABLE temp_jsonl (data jsonb);
-- 导入JSONL文件
COPY temp_jsonl FROM '/path/to/your/target.jsonl';

2. 筛选新增记录插入目标表（适配jsonb_populate_record写法）

INSERT INTO musicbrainz.acoustid_meta
SELECT t.*
FROM temp_jsonl tj
CROSS JOIN jsonb_populate_record(null::musicbrainz.acoustid_meta, tj.data) t
-- 核心筛选条件：判断JSON对象存在created顶层键
WHERE tj.data ? 'created';

扩展：同时处理新增+更新场景 #

如果要在同一个任务里同时处理新增和更新逻辑，可以直接用UPSERT实现，无需拆分任务：

INSERT INTO musicbrainz.acoustid_meta
SELECT t.*
FROM temp_jsonl tj
CROSS JOIN jsonb_populate_record(null::musicbrainz.acoustid_meta, tj.data) t
ON CONFLICT (id) DO UPDATE 
SET 
    track = EXCLUDED.track,
    artist = EXCLUDED.artist,
    album = EXCLUDED.album,
    album_artist = EXCLUDED.album_artist,
    track_no = EXCLUDED.track_no,
    disc_no = EXCLUDED.disc_no,
    year = EXCLUDED.year
-- 仅标记为updated的记录才执行更新
WHERE tj.data ? 'updated';

以上逻辑可以直接复用到所有表的导入场景，只需替换目标表名、文件路径即可，无需单独写转换脚本。
如果临时表存储的是json类型而非jsonb类型，将筛选条件替换为WHERE json_exists(tj.data, '$.created')即可。 #

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