SNOWFLAKE Error parsing JSON: incomplete object value when parsing a Json file in snowflake worksheet. (json file is verified and correct)

Question

the problem is I have a json file stored in a stage in one of my databases (newly generated) I am not performing any database related activities I'm just trying to query the json data using :

SELECT parse_json($1):order_id FROM @my_stage/mahdi_test.json.gz t;

and here is the mahdi_test.json sample :

{"order_id": 67, "file_id": *****, "file_name": "name.pdf", "file_type": "pdf", "language_id": 1, "created_dt": "2030-11-17 19:39:25", "delivery_id": *****},

(the "*" are just to avoid showing actual data.)

the json file contains multiple lines just like the sample above ... but the results of the query is :

"Error parsing JSON: incomplete object value, pos 17"

the most tricky part is that I took the same json file into another DB's stage (this database was generated before and not by me) and tried the same thing in the snowflake worksheet ( I changed the database in the panel on top right side of worksheet to the older DB) and started the exact same query with the exact same json file ... but this time it worked and it showed me the results.

what is causing this problem how can I make the new database to act like the other one, because clearly there is nothing wrong with the json file itself because it worked on the legacy database.

Is there a file format tied to the stage in one database and not the other database? — Mike Walton
– Mike Walton, Commented Dec 28, 2020 at 22:41
@MikeWalton yes you are right, I did not specify the type of file format when generating the stage, I tried adding the json file format to the stage and the process was a success, thank you very much — Mahdi
– Mahdi, Commented Dec 31, 2020 at 12:59
You're welcome. If you have a chance, please accept the actual answer below. — Mike Walton
– Mike Walton, Commented Jan 1, 2021 at 19:59

Mike Walton · Accepted Answer · 2021-01-01 19:59:10Z

3

The answer to this question ended up being that the stage did not have a file format specified. Adding a file format that specified a JSON format to the stage fixed the issue.

answered Jan 1, 2021 at 19:59

Mike Walton

7,4592 gold badges14 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

SNOWFLAKE Error parsing JSON: incomplete object value when parsing a Json file in snowflake worksheet. (json file is verified and correct)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related