Need help with JSON flatted file and Bulk insert Snowflake block

[MartijnWanders]

Hi People,

In the attachment is a Automation. I want to flatten the JSON file and insert this in Snowflake. In the Output block you can see what I'm expecting.

I flatten the file in vList. If I want to map a column in vObject I get no output to do so. It must be simple but I don't see it anymore.

Can somebody help me 🙂

Thank you!

[Madhushree_BP]

Hi @MartijnWanders ,

Do you want to map the values of the Flatten vList in the vObject such that you can output using the output block? Let me know if my understanding is correct.

If so, Did you try using the operation "Set equal to object" in the vObject block? I have updated the automation and shared please have a look.

Let me know if this is what you expected as the output.

Thanks,
Madhushree

[MartijnWanders]

Hi Madhushree,

That is exactly what I want but instead of the Output block it must be in the Snowflake Bulk block. That block is expecting a JSON file. I don't know the "Set equal to object" function. I tried but get an error, see below.

error:

SQLSTATE[42000]: Syntax error or access violation: 1003 SQL compilation error: syntax error line 1 at position 134 unexpected '.'. (SQL: insert into "LANDING"."test" ("category"."id", "category"."name", "id", "name", "photoUrls"."0", "photoUrls"."1", "status", "tags"."0"."id", "tags"."0"."name", "tags"."1"."id", "tags"."1"."name") values (-93773065, ullamco pariatur qui, 33497253, doggie, incididunt adipisicing, do Excepteur dolor, pending, -86565557, dolor in deserunt , 8624085, laborum nisi consequat magna), (-96087438, eu qui, 9223372036854768481, doggie, deserunt nostrud pariatur do, exercitation, pending, 2238351, officia amet aute, 72981749, in in voluptate dolore fugiat), (-93970098, nulla occaecat eiusmod cupidatat mollit, 9223372036854768482, doggie, fugiat dolor est reprehenderit, ipsum magna ea nisi, pending, -12746721, consequat ex, 29114087, et eu anim ut), (58104089, do fugiat laborum ut, 16070974, dog, Ut nisi Excepteur do, enim ullamco adipisicing sunt, pending, 95465410, non laborum cupidatat officia, 23512356, esse amet Duis), (46161566, aliquip in dol, 32588349, doggie, Excepteur nulla voluptate labore esse, elit non, pending, -18619507, laboris culpa, 38532824, cillum), (9223372036854768649, freshwater goby, https://www.kaggle.com/qiriro, pending, Array), (9223372036854768653, dace, https://www.kaggle.com/chaitanyakck, pending, Array), (9223372036854768657, kory plummer, https://www.kickstarter.com/projects/2038173948/paper-chase-students-can-make-family-friendly-shor, pending, Array), (9223372036854768660, anchovy, https://www.kickstarter.com/projects/ricksbees/sweetness-of-beeing-expanding-the-apiary, pending, Array), (9223372036854768663, goldfish, https://www.kaggle.com/hidehisaarai1213, pending, Array))

It's struggling with the . in columns names I think.

[Madhushree_BP]

Hi @MartijnWanders ,

Yes, the error is due to the column names with a dot. 

As we are not able to manipulate the column values, the best approach is to add an object variable(vObject) with hardcoded column values that are accepted by the snowflake, and then add it to a list variable (vList) which should work fine.

I have updated the automation and shared please have a look. Let me know if this solves your issue.

Thanks,

Madhushree

[MartijnWanders]

Hi @Madhushree_BP ,

This is almost what I want. How do I make a combination between the flatten option and the vList, vObject and vTableList? I get no output from vList? So I can not map the fields properly in vObject.

If you look at field Tag it's nested. Normally I would place another loop to fetch this data but now I want to do it with the flatten option. So I can delete the extra loop. In my customer case I have a Automation with 5 loops and doesn't work properly at the last one. If we make this example automation work then I can implement this in the real situation.

Do you have a solution for this? Thank you!

[Madhushree_BP]

Hi @MartijnWanders ,

I have modified the automation and shared the JSON, please have a look.

Please check the column names in your snowflake DB and change them accordingly in your automation. Have a look at the mapped values in vObj.

It is not required to flatten the nested JSON here and also it is not possible to map the list variables(vList) output in this case, If you want to map the values in vObj from vList, you need to add a loop block to do it, and in this scenario, it's not needed because you already have a loop block with all the info.

Thanks!

[MartijnWanders]

Hi @Madhushree_BP ,

In my real case I have 5 loops needed to get the data. On the last loop I get a error because it can not itterate on a value field.

For this I created a older threat: https://community.qlik.com/t5/Qlik-Application-Automation/Create-from-value-a-valid-iterable-object-...

I got a solution and Extract data with an QlikSense App. But this isn't the most ideal solution.

This threat was by B option but I understand that isn't possible with the flatten option?

[Madhushree_BP]

Hi @MartijnWanders ,

What is the error you are getting? Can you please share the screenshot of the same also can we know the exact use case?

Thanks,

Madhushree

[MartijnWanders]

Hi @Madhushree_BP ,

In the attachment you can find a screenshot.