cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TexasBI
Level: Powered On

Build JSON object

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:

 

{
"Invoice Name": "My Invoice",
"Description": "My Description",
"Items":[
{
"FieldValues":{
"Customer":"Bob",
"Amount":"10",
"Quantity":"5",
"Start Date": "2018-05-01 19:29:00.000",
"End Date": "2018-05-01 19:29:00.000"
}
},
{
"FieldValues":{
"Customer":"Joe",
"Amount":"11",
"Quantity":"2",
"Start Date""2018-05-01 19:29:00.000",
"End Date""2018-05-01 19:29:00.000"
}
},
]

}
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Build JSON object

Hi @TexasBI,

 

I have made a test on my side and please take a try with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a "Get rows" action, specify Table name.
  • Add a Variables-> "Initialize variable" action, Name set to ItemsArray, Type set to Array and Value set to empty.
  • Add a "Apply to each" action, input parameter set to output of the "Get rows" action.
  • Within "Apply to each" action, add a Compose action, Inputs set to following formula:
{
  "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
   }
}

11.JPG

Add a "Append to array variable" action, Name chose ItemsArray and Value set to output of the "Compose" action.

 

  • Under "Apply to each" action, add a "Compose 2" action, Inputs set to following formula:
{
  "Invoice Name":"My Invoice",
   "Description":"My Description",
   "Items":ItemsArray variable
}

12.JPG

Image reference:
13.JPG

 

14.JPG

The flow works successfully as below:15.JPG

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Build JSON object

Hi @TexasBI,

 

I have made a test on my side and please take a try with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a "Get rows" action, specify Table name.
  • Add a Variables-> "Initialize variable" action, Name set to ItemsArray, Type set to Array and Value set to empty.
  • Add a "Apply to each" action, input parameter set to output of the "Get rows" action.
  • Within "Apply to each" action, add a Compose action, Inputs set to following formula:
{
  "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
   }
}

11.JPG

Add a "Append to array variable" action, Name chose ItemsArray and Value set to output of the "Compose" action.

 

  • Under "Apply to each" action, add a "Compose 2" action, Inputs set to following formula:
{
  "Invoice Name":"My Invoice",
   "Description":"My Description",
   "Items":ItemsArray variable
}

12.JPG

Image reference:
13.JPG

 

14.JPG

The flow works successfully as below:15.JPG

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

tirandagan
Level: Powered On

Re: Build JSON object

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
    }]
}
Highlighted
tirandagan
Level: Powered On

Re: Build JSON object

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
    }]
}

 

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (5,419)