Hello,
I am trying to build a JSON from a SQL table. My SQL Tabel has 5 columns. and in the Table has 25 rows I would like to build a JSON that pulls the data from this table. I am having trouble geting flow to populate the items in the JSON. more rows will be added and I want the JSON items to expand.
SQL Table columns:
Customer
Start Date
End Date
Amount
Quantity
I would like my JSON to look like this:
Solved! Go to Solution.
Hi @TexasBI,
I have made a test on my side and please take a try with the following workaround:
{ "FieldValues": { "Customer": Customer dynamic content of the "Get rows" action, "Amount":Amount dynamic content of the "Get rows" action, "Quantity":Quantity dynamic content of the "Get rows" action, "Start Date": Start Date dynamic content of the "Get rows" action, "End Date": End Date dynamic content of the "Get rows" action } }
Add a "Append to array variable" action, Name chose ItemsArray and Value set to output of the "Compose" action.
{ "Invoice Name":"My Invoice", "Description":"My Description", "Items":ItemsArray variable }
Image reference:
The flow works successfully as below:
The output of the "Compose 2" action as below:
{ "Invoice Name": "My Invoice", "Description": "My Description", "Items": [ { "FieldValues": { "Customer": "Bob", "Amount": "10", "Quantity": "5", "Start Date": "2018-05-01T00:00:00Z", "End Date": "2018-05-02T00:00:00Z" } }, { "FieldValues": { "Customer": "Joe", "Amount": "11", "Quantity": "2", "Start Date": "2018-05-03T00:00:00Z", "End Date": "2018-05-04T00:00:00Z" } } ] }
Best regards,
Kris
Hi @TexasBI,
I have made a test on my side and please take a try with the following workaround:
{ "FieldValues": { "Customer": Customer dynamic content of the "Get rows" action, "Amount":Amount dynamic content of the "Get rows" action, "Quantity":Quantity dynamic content of the "Get rows" action, "Start Date": Start Date dynamic content of the "Get rows" action, "End Date": End Date dynamic content of the "Get rows" action } }
Add a "Append to array variable" action, Name chose ItemsArray and Value set to output of the "Compose" action.
{ "Invoice Name":"My Invoice", "Description":"My Description", "Items":ItemsArray variable }
Image reference:
The flow works successfully as below:
The output of the "Compose 2" action as below:
{ "Invoice Name": "My Invoice", "Description": "My Description", "Items": [ { "FieldValues": { "Customer": "Bob", "Amount": "10", "Quantity": "5", "Start Date": "2018-05-01T00:00:00Z", "End Date": "2018-05-02T00:00:00Z" } }, { "FieldValues": { "Customer": "Joe", "Amount": "11", "Quantity": "2", "Start Date": "2018-05-03T00:00:00Z", "End Date": "2018-05-04T00:00:00Z" } } ] }
Best regards,
Kris
No need to build a complex flow. This can be easily done within T-SQL on your SQL server/Azure SQL:
The following query:
SELECT Customer, StartDate, EndDate, Amount, Quantity FROM dbo.Customers FOR JSON PATH, ROOT ('Items') GO
Will result in:
{ "Items": [{ "Customer": "Jim Davidson", "StartDate": "2018-01-01", "EndDate": "2018-03-01", "Amount": 420, "Quantity": 16 }, { "Customer": "Donald Duck", "StartDate": "2018-02-01", "EndDate": "2018-05-02", "Amount": 220, "Quantity": 8 }, { "Customer": "James Bond", "StartDate": "1967-06-01", "EndDate": "1998-01-05", "Amount": 9910, "Quantity": 23 }] }
and the following is a step closer that what you are looking for:
SELECT JSON_MODIFY( '{ "Invoice Name": "My Invoice", "Description": "My Description" }','$.Items', (SELECT Customer, StartDate, EndDate, Amount, Quantity FROM dbo.Customers FOR JSON PATH) ) GO
Results in:
{ "Invoice Name": "My Invoice", "Description": "My Description", "Items": [{ "Customer": "Jim Davidson", "StartDate": "2018-01-01", "EndDate": "2018-03-01", "Amount": 420, "Quantity": 16 }, { "Customer": "Donald Duck", "StartDate": "2018-02-01", "EndDate": "2018-05-02", "Amount": 220, "Quantity": 8 }, { "Customer": "James Bond", "StartDate": "1967-06-01", "EndDate": "1998-01-05", "Amount": 9910, "Quantity": 23 }] }
User | Count |
---|---|
88 | |
40 | |
23 | |
20 | |
16 |
User | Count |
---|---|
128 | |
49 | |
47 | |
35 | |
25 |