Problem loading JSON with comma into Snowflake

1
votes

I am having an issue loading some JSON data.

The data looks like this:

{"geometry":{"coordinates":[12.5263,55.7664],"type":"Point"},"properties":{"created":"2021-01-19T17:08:14.114216Z","observed":"2020-01-01T23:50:00Z","parameterId":"pressure_at_sea","stationId":"06181","value":1025.1},"type":"Feature","id":"00e3bc2b-9a55-03dc-3740-005fd752f840"}
{"geometry":{"coordinates":[10.6217,55.8315],"type":"Point"},"properties":{"created":"2021-01-19T23:26:37.906088Z","observed":"2020-01-01T23:50:00Z","parameterId":"radia_glob","stationId":"06132","value":1},"type":"Feature","id":"00f7c039-6096-e2c2-5063-594c1c3bc16e"}
{"geometry":{"coordinates":[-37.6367,65.6111],"type":"Point"},"properties":{"created":"2021-01-19T23:26:37.913180Z","observed":"2020-01-01T23:50:00Z","parameterId":"radia_glob","stationId":"04360","value":0},"type":"Feature","id":"0142e2d1-9c28-e884-8d88-4b8fac5cae5d"}

The challenge seems to be, when I try to look at the JSON data like;

select $1, metadata$filename from @my_bucket/2020/2020-01.json.gz limit 3;

It only returns a part of the JSON:

$1  METADATA$FILENAME
{"geometry":{"coordinates":[12.5263     2020/2020-01.json.gz
{"geometry":{"coordinates":[10.6217     2020/2020-01.json.gz
{"geometry":{"coordinates":[-37.6367    2020/2020-01.json.gz

Seems like everything after the comma in the coordinates gets truncated, but I cannot figure out how to avoid that.

Br.

Thomas

1
jsonsnowflake-cloud-data-platform

1 Answers

0
votes

$1 is column one, which helps show that your data is being treated as CSV. Which is the default file format.

try adding file format FILE_FORMAT => 'my_json_file' which would be created via:

CREATE OR REPLACE FILE FORMAT my_json_file TYPE = JSON;