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
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

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

Users online (2,624)