Use case to load json as one row instead of many, dataset specific

Question

I am working with two types of json files and am running into an error that seems to be recommending that I load each file into a different row, since I broke up the files into smaller ones. However when I want to load one file into multiple rows to help with the number of flattens later. (all data is dummy data for privacy)

Here for example is what I am running into am not sure if I can combine the copy into and select, I will include the file format definition below as well. Data broken up to multiple files, using:

File Format:

file_format = (type = 'JSON' strip_outer_array = true);

Data Sample

[{"name":"Bigtax","version":"2.2.9","color":"Indigo","available":false},
{"name":"Solarbreeze","version":"7.00","color":"Khaki","available":false},
{"name":"Toughjoyfax","version":"0.7.1","color":"Turquoise","available":false},
{"name":"Otcom","version":"0.95","color":"Indigo","available":false}]

There is another format I am trying to upload the whole file into multiple rows:

[
	{
		color: "red",
		value: "#f00"
	},
	{
		color: "green",
		value: "#0f0"
	},
	{
		color: "blue",
		value: "#00f"
	},
	{
		color: "cyan",
		value: "#0ff"
	},
	{
		color: "magenta",
		value: "#f0f"
	},
	{
		color: "yellow",
		value: "#ff0"
	},
	{
		color: "black",
		value: "#000"
	}
]

Where I am loading to internal stage and want to copy into a table like this:

copy into Colors from @~/staged 
   file_format = (format_name = 'json');
   
   
   SELECT
  value:name::string as "Color Name",
  value:hex::string as "Hex"
  FROM
    file
  , LATERAL FLATTEN(INPUT => SRC:Colors);

Mike Walton Mike Walton · Accepted Answer · 2020-01-16T23:42:28

I recommend taking a look at this part of Snowflake documentation. It is important to note that in order for Snowflake to correctly load a JSON document into separate records in a table, it needs to be NDJSON format. The row terminators become very important:

https://docs.snowflake.net/manuals/user-guide/data-load-transform.html#supported-file-formats

Use case to load json as one row instead of many, dataset specific

1 Answers