问题:PostgreSQL json 类型的输入语法无效 输入字符串意外结束

当我尝试在 PostgreSQL pgadmin 上导入 JSON 文件时,我编写了以下脚本,但由于某种原因,它无法处理下面显示的错误。

sql/plpgsql:

DROP TABLE IF EXISTS temp01;
DROP TABLE IF EXISTS temp_json;
create temp table temp01 (
    tmp text,
    tmp02 text,
    tmp03 text,
    tmp04 text
)
with (oids = false);

BEGIN;
create temporary table temp_json (values text) on commit drop;
copy temp_json from '/home/yuis/pg/psql/tmp03.json';

insert into temp01
select values->>'id' as tmp,
       values->>'created_at' as tmp02,
       values->>'username' as tmp03,
       values->>'tweet' as tmp04
    from (
        select replace(values,'\','\\')::json as values from temp_json 
    )
COMMIT;

SELECT * from temp01;

上面的结果应该是这样的表:

tmp|tmp02|tmp03|tmp04 
1396415271359897603,2021-05-23 19:38:39 JST,themooncarl,@elonmusk is still on our side.  t.co/K5DnByjzic
1396414423057711109,2021-05-23 19:35:17 JST,..(and so on)

错误:

ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: 
SQL state: 22P02

JSON 文件“tmp03.json”:

{"id": 1396415271359897603, "conversation_id": "1396415271359897603", "created_at": "2021-05-23 19:38:39 JST", "date": "2021-05-23", "time": "19:38:39", "timezone": "+0900", "user_id": 978732571738755072, "username": "themooncarl", "name": "The Moon 🌙", "place": "", "tweet": "@elonmusk is still on our side.  t.co/K5DnByjzic", "language": "en", "mentions": [], "urls": [], "photos": ["https://pbs.twimg.com/media/E2EQSZgWQAELw9T.jpg"], "replies_count": 78, "retweets_count": 47, "likes_count": 570, "hashtags": [], "cashtags": [], "link": "https://twitter.com/TheMoonCarl/status/1396415271359897603", "retweet": false, "quote_url": "", "video": 1, "thumbnail": "https://pbs.twimg.com/media/E2EQSZgWQAELw9T.jpg", "near": "", "geo": "", "source": "", "user_rt_id": "", "user_rt": "", "retweet_id": "", "reply_to": [], "retweet_date": "", "translate": "", "trans_src": "", "trans_dest": ""}
{"id": 1396414423057711109, "conversation_id": "1396414423057711109", "created_at": "2021-05-23 19:35:17 JST", "date": "2021-05-23", "time": "19:35:17", "timezone": "+0900", "user_id": 978732571738755072, "username": "themooncarl", "name": "The Moon 🌙", "place": "", "tweet": "Me watching Bitcoin go down but realizing that it’s just a nice opportunity to buy more for cheap.  t.co/GkmSEPmJCh", "language": "en", "mentions": [], "urls": [], "photos": ["https://pbs.twimg.com/media/E2EPg4ZXMAMIXjJ.jpg"], "replies_count": 94, "retweets_count": 34, "likes_count": 771, "hashtags": [], "cashtags": [], "link": "https://twitter.com/TheMoonCarl/status/1396414423057711109", "retweet": false, "quote_url": "", "video": 1, "thumbnail": "https://pbs.twimg.com/media/E2EPg4ZXMAMIXjJ.jpg", "near": "", "geo": "", "source": "", "user_rt_id": "", "user_rt": "", "retweet_id": "", "reply_to": [], "retweet_date": "", "translate": "", "trans_src": "", "trans_dest": ""}
{"id": 1396388111840645120, "conversation_id": "1396388111840645120", "created_at": "2021-05-23 17:50:44 JST", "date": "2021-05-23", "time": "17:50:44", "timezone": "+0900", "user_id": 978732571738755072, "username": "themooncarl", "name": "The Moon 🌙", "place": "", "tweet": "HODL!!! 💪", "language": "cs", "mentions": [], "urls": [], "photos": [], "replies_count": 263, "retweets_count": 149, "likes_count": 2299, "hashtags": [], "cashtags": [], "link": "https://twitter.com/TheMoonCarl/status/1396388111840645120", "retweet": false, "quote_url": "", "video": 0, "thumbnail": "", "near": "", "geo": "", "source": "", "user_rt_id": "", "user_rt": "", "retweet_id": "", "reply_to": [], "retweet_date": "", "translate": "", "trans_src": "", "trans_dest": ""}

虽然上面显示“类型 json 的输入语法无效”错误,但下面,使用我在 SO 帖子中找到的更简单的 JSON 示例,此操作成功且没有语法错误。

DROP TABLE IF EXISTS temp01;
DROP TABLE IF EXISTS temp_json;
create temp table temp01 (
    tmp text,
    tmp02 text,
    tmp03 text,
    tmp04 text
)
with (oids = false);

BEGIN;
create temporary table temp_json (values text) on commit drop;
-- copy temp_json from '/home/yuis/pg/psql/tmp03.json';
copy temp_json from '/home/yuis/pg/psql/tmp.json';

insert into temp01
-- select values->>'id' as tmp,
--        values->>'created_at' as tmp02,
--     values->>'username' as tmp03,
--     values->>'tweet' as tmp04
select values->>'id' as tmp,
    values->>'name' as tmp02,
    values->>'comment' as tmp03
    from (
        select replace(values,'\','\\')::json as values from temp_json
    )
COMMIT;

SELECT * from temp01;

JSON,“tmp.json”:

{"id": 23635,"name": "Jerry Green","comment": "Imported from facebook."}
{"id": 23636,"name": "John Wayne","comment": "Imported from facebook."}

因此,显然这里的问题来自 JSON 的语法错误,但正如您所看到的 JSON 没有语法错误,显然问题出在 SQL 方面。我不知道 JSON 和/或 SQL 哪里出错了。

解答

经过一些尝试,正如@jjanes 提到的,我找到了这个问题的原因,它是因为 json 文件(tmp03.json)末尾的空行。

当我使用“cat > file”复制并粘贴到文件中时,我无意中在行尾按下了一个不必要的回车键,结果在 json 文件的末尾创建了一个空行。所以,这条线导致了错误。叹..

这里我做了一些额外的尝试来进一步理解这个问题。

• tmp05.json,删除最后一个“空”新行的 json 行

工作

• tmp03.json,json行,最后一个空行(问题提到错误)

ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1:
SQL state: 22P02

• tmp05_b.json,json行但只有一行,没有新行

例如很喜欢

{"a": "aa"}{"b": "bb"}{"c": "cc"}

而不是

{"a": "aa"}
{"b": "bb"}
{"c": "cc"}

ERROR:  invalid input syntax for type json
DETAIL:  Expected end of input, but found "{".
CONTEXT:  JSON data, line 1: ...anslate": "", "trans_src": "", "trans_dest": ""}{...
SQL state: 22P02

• tmp05_c.json,json 行,但删除最后一个新行

删除了最后一个空行以及工作的 tmp05.json,但也删除了行尾的最后一个新行。

工作