Build JSON object

[TexasBI]

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"

}

},

]

}

[v-xida-msft · Accepted solution]

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

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
}

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

[tirandagan]

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

[tirandagan]

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