CODEWITHSUNDEEP
jsonsnowflake-cloud-data-platformazure-data-factory
TonyGaul
TonyGaul

Reputation: 21

Snowflake parse multiple line JSON

Fairly new to Snowflake but have loaded data successfully via various methods.

Problem: I am calling a Web API via Azure Data Factory using the pagination rules to write to a single JSON file in blob storage. This is done using multiple calls via single ADF data copy activity utilising the AbsoluteURL to merge to a single file (I could write extra, complex ADF logic to generate multiple files, but for this example want to go with a single file approach). This is really efficient in my ADF pipeline and I can either write as an array or set of objects.

The ADF part works well, but I am having trouble parsing the "merged" JSON file when loading into a table of rows in Snowflake via a stage.

The output JSON file called "setOfObjects.json" looks like this for ADF JSON sink setting: setOfObjects (JSON lines):

{"values":[{"a":"1","b":"1","c":"1"},{"a":"2","b":"2","c":"2"}]}
{"values":[{"a":"3","b":"3","c":"3"},{"a":"4","b":"4","c":"4"}]}
<empty line>

In Snowflake, I create a stage such as

create or replace stage dbo.stage_json_example url = 'azure://<endpoint>.blob.core.windows.net/test/stackOverflow/'
credentials = (azure_sas_token = '<sas_token>')
file_format = (type = 'json');

Then in Snowflake, I try to parse the JSON file such as, but it returns NULL value:

select $1:values:a::string
from @dbo.stage_json_example/setOfObjects.json;

From above, I want to convert the single JSON file into 4 rows being:

a b c
1 1 1
2 2 2
3 3 3
4 4 4

Any suggestions are appreciated. Thanks

Upvotes: 0

Views: 2761

Answers (2)

TonyGaul
TonyGaul

Reputation: 21

OK, found the answer from this link https://community.snowflake.com/s/article/json-data-parsing-in-snowflake

This Snowflake code solved the problem.

select t.value:a::int as "a", t.value:b::int as "b", t.value:c::int  as "c"
from @dbo.stage_json_example/setOfObjects.json as S
, table(flatten(S.$1,'values')) t;

Upvotes: 1

Michael Planer
Michael Planer

Reputation: 51

Perhabs you try to write a { at the beginning and } at the end of your document. JSON always needs a document root. With this change you have one.

so your Example would looks like:

{
 {"values":[{"a":"1","b":"1","c":"1"},{"a":"2","b":"2","c":"2"}]}
 {"values":[{"a":"3","b":"3","c":"3"},{"a":"4","b":"4","c":"4"}]} 
}

Upvotes: 0

Related Questions