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.

View solution in original post

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,494)