CODEWITHSUNDEEP
snowflake-cloud-data-platform
Mahdi
Mahdi

Reputation: 13

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

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.

Upvotes: 1

Views: 2438

Answers (1)

Mike Walton
Mike Walton

Reputation: 7339

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.

Upvotes: 2

Related Questions