cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TexasBI
Frequent Visitor

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
v-xida-msft
Community Support
Community Support

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
v-xida-msft
Community Support
Community Support

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.

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

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (1,997)