Convert data from SQL select to JSON

[peter]

What is the best way to take data from a SQL Select statement and convert to JSON so that it can be used in a API put commend?

[RogerSramkoski]

Hello Peter, welcome to the SnapLogic Community! SnapLogic is a JSON-based platform so what that means to you while working in the Designer is that every snap with a circular input or output is communicating through JSON, including the SQL Server Select snap results or a SELECT statement written in SQL Server Execute snap. Here’s a screenshot example:

[peter]

Great that worked a treat.

Thanks

[darshthakkar]

@rsramkoski - can we add one more layer to this JSON structure? If yes, how could we do that?
I tried using Group By N with Group Size = 1 however I’m not getting the desired output.

Screenshot after using Group By N:

Desired Output:

Need to add one more layer so that the JSON output becomes nested.
Thanking in advance for your help.

Regards,
Darsh

[Abhishek_Soni37 · Accepted solution]

Hi @darshthakkar,

Based on the screenshot provided, I created a dummy JSON and used extend expression in Mapper to add an extra layer.

Input JSON:

Mapper Expression:

Final Result:

Pipeline:
Test_Expression_2023_06_18.slp (3.5 KB)

I hope this helps.

Cheers 🙂