PostgreSQL invalid input syntax for type json The input string ended unexpectedly

Question

As I'm trying to import JSON file on PostgreSQL pgadmin, I wrote following script, but it didn't work with the error shown below, for some reason.

The 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;

The above should have resulted a table something like this:

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)

The error:

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

The JSON file, "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": ""}

While the above showing "invalid input syntax for type json" error, the below, with a more simpler sample JSON I found on a SO post, this successes with no syntax error.

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;

The JSON, "tmp.json":

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

So, apparently the problem here is from the syntax error of the JSON, but as you can see the JSON has no syntax error, apparently the problem is on the SQL side. I have no idea where in the JSON and/or SQL is wrong.

Cut and paste your JSON to a validator (such as jsonformatter.org) Make sure it validates before attempting to load to SQL. A quick look tells me that the ID field is being considered numeric, and that value is too large for a number. I suggest wrapping that value in quotes and treat as a string. There could be other errors as well... — Sparky
– Sparky, Commented May 24, 2021 at 2:19
Your code works fine for me with the text-formatted file 'tmp03.json' which you showed. Maybe the process of copying and pasting it into the html text box cleaned up the problem — jjanes
– jjanes, Commented May 24, 2021 at 2:32

ー PupSoZeyDe ー · Accepted Answer · 2021-05-24 03:00:55Z

4

After some tryings, as @jjanes mentioned, I found the cause of this problem, it was beucase an empty line in the end of the json file (tmp03.json).

As I copy and pasted to the file using "cat > file" and I inadvertently pressed one unnecessary enter key in the end of the lines, it resulted creating a empty line in the end of the json file. So, this line caused the error. sigh..

Here some extra tryings I made for further understanding around this issue.

tmp05.json, lines of json with removed last "empty" new line

worked

tmp03.json, lines of json with an empty line in the last (the question mentioned error)

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

tmp05_b.json, lines of json but only one line and no new lines

e.g. so like

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

rather than

{"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, lines of json but remove last new line

removed the last empty line as well as the working tmp05.json, but also removed the last new line of the end of lines.

worked

answered May 24, 2021 at 3:00

ー PupSoZeyDe ー

1,4128 gold badges23 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IcedDante Over a year ago

similar to an errant dataset, any "poison pill" records in your db can break these queries

mohammed_ayaz · Accepted Answer · 2024-06-03 06:46:03Z

0

For me, it worked when I filtered the rows.

SELECT id, column_name
FROM schema.table_name 
WHERE column_name IS NOT NULL  
AND (column_name !~ '^\s*\{.*\}\s*$' OR column_name !~ '^\s*\[.*\]\s*$');

Then I got the rows where the data is invalid. I deleted those rows.

delete  from schema.table_name   where id ='553';

Then I converted the rows to jsonb from varchar.

answered Jun 3, 2024 at 6:46

mohammed_ayaz

69712 silver badges19 bronze badges

Collectives™ on Stack Overflow

PostgreSQL invalid input syntax for type json The input string ended unexpectedly

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related