topic Re: Need help with JSON flatted file and Bulk insert Snowflake block in Qlik Automate

Need help with JSON flatted file and Bulk insert Snowflake block

MartijnWanders — Wed, 01 Mar 2023 12:12:08 GMT

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!

Re: Need help with JSON flatted file and Bulk insert Snowflake block

Madhushree_BP — Thu, 09 Mar 2023 05:43:59 GMT

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

Re: Need help with JSON flatted file and Bulk insert Snowflake block

MartijnWanders — Thu, 09 Mar 2023 06:22:18 GMT

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.

Re: Need help with JSON flatted file and Bulk insert Snowflake block

Madhushree_BP — Fri, 10 Mar 2023 05:46:50 GMT

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

Re: Need help with JSON flatted file and Bulk insert Snowflake block

MartijnWanders — Sat, 11 Mar 2023 05:14:26 GMT

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!

Re: Need help with JSON flatted file and Bulk insert Snowflake block

Madhushree_BP — Mon, 13 Mar 2023 10:28:04 GMT

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!

Re: Need help with JSON flatted file and Bulk insert Snowflake block

MartijnWanders — Tue, 14 Mar 2023 10:19:50 GMT

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-in-REST-automation/m-p/2043894#M1413

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?

Re: Need help with JSON flatted file and Bulk insert Snowflake block

Madhushree_BP — Thu, 16 Mar 2023 06:21:34 GMT

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

Re: Need help with JSON flatted file and Bulk insert Snowflake block

MartijnWanders — Thu, 16 Mar 2023 10:32:08 GMT

Hi @Madhushree_BP ,

In the attachment you can find a screenshot.