Answer a 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.

Answers

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