PostgreSQL提取JSON数据报错及数据拆分需求求助

问题根源 #

COPY命令默认逐行读取文件，而你的JSON是多行结构，导致每一行被当作单独记录存入tmp表。第一行仅为{，并非合法JSON，因此后续解析时触发语法错误。

解决步骤 #

1. 正确导入JSON数据 #

方法一：用空字符作为分隔符导入完整JSON

直接创建jsonb类型的表，通过指定特殊分隔符确保整个JSON文件作为单条记录导入：

CREATE TABLE tmp (c jsonb);
COPY tmp FROM 'C:\temp\spj2.json' WITH (FORMAT text, DELIMITER E'\x00');

方法二：直接读取文件内容（需超级权限）

如果文件体积较小，可通过pg_read_file函数直接读取并转换为JSON类型：

CREATE TABLE tmp AS
SELECT pg_read_file('C:/temp/spj2.json')::jsonb AS c;

2. 提取基础字段数据 #

导入完成后，执行以下查询即可获取目标格式的基础数据：

SELECT
    c ->> 'SerialNumber' AS SerialNumber,
    c ->> 'BeginningDate' AS BeginningDate,
    c ->> 'EndDate' AS EndDate
FROM tmp;

查询结果：

SerialNumber |      BeginningDate      |        EndDate
--------------+-------------------------+-------------------------
 940860       | 2023-05-20T11:53:39     | 2023-05-22T09:04:04

3. 拆分ChannelIDs到临时表 #

使用jsonb_array_elements_text拆分JSON数组，结合row_number()生成序号，创建临时表：

CREATE TEMP TABLE channel_ids AS
SELECT
    row_number() OVER (ORDER BY (elem::int)) AS ID,
    elem::int AS ChannelID
FROM tmp,
     jsonb_array_elements_text(c -> 'ChannelIDs') AS elem;

查询临时表结果：

ID | ChannelID
----+-----------
  1 |        11
  2 |        12
  3 |        13
  4 |        14
  5 |        17

注：若需保留原数组顺序，删除ORDER BY (elem::int)即可。

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