Azure SQL bindings for Azure Functions serialize input

Question

I need to create a function app in azure returning data in json format from azure sql database. I followed this article using Azure SQL bindings for Azure Functions with this similar code.

// Copyright (c) Microsoft Corporation. All rights reserved.
// Licensed under the MIT License. See License.txt in the project root for license information.  
using Microsoft.AspNetCore.Http; 
using Microsoft.AspNetCore.Mvc; 
using Microsoft.Azure.WebJobs.Extensions.Http;
namespace Microsoft.Azure.WebJobs.Extensions.Sql.Samples.InputBindingSamples
{ public static class GetProductsString
{ [FunctionName("GetProductsString")]
public static IActionResult Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts-string/{cost}")]  HttpRequest req,
[Sql("select * from Products where cost = @Cost",
"SqlConnectionString",parameters: "@Cost={cost}")]
string products)         {             
// Products is a JSON representation of the returned rows. For example, if there are two returned rows,             
// products could look like:             
// [{"ProductId":1,"Name":"Dress","Cost":100},{"ProductId":2,"Name":"Skirt","Cost":100}]             return new OkObjectResult(products);         }     } }

It's OK but I need to serialize the input:

instead of:

[
{"ProductId":1,
"Name":"Dress",
"Cost":100},
{"ProductId":2,
"Name":"Skirt",
"Cost":100}
]

I need

[
{"ProductId":1,
"clothes":{
	"Name":"Dress",
	"Cost":100}
},
{"ProductId":2,
"clothes":{
	"Name":"Skirt",
	"Cost":100}
]

Answer 1

Hi @Nguyen Yann

There are two options to go about getting that result. The first is using the FOR JSON clause in your T-SQL statement. I didn't see anything in the docs illustrating but it's something worth testing.

SELECT ProductId, JSON_QUERY(JSON_OBJECT('Name', Name, 'Cost', Cost)) AS clothes FROM Products WHERE Cost = @Cost FOR JSON PATH

The second option is serializing the output yourself.

using Microsoft.AspNetCore.Http; 
using Microsoft.AspNetCore.Mvc; 
using Microsoft.Azure.WebJobs.Extensions.Http;
using Newtonsoft.Json;
using System.Collections.Generic;

namespace Microsoft.Azure.WebJobs.Extensions.Sql.Samples.InputBindingSamples
{ 
    public static class GetProductsString
    { 
        [FunctionName("GetProductsString")]
        public static IActionResult Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts-string/{cost}")]  HttpRequest req,
            [Sql("select * from Products where cost = @Cost", "SqlConnectionString", parameters: "@Cost={cost}")]
            IEnumerable<Product> products)         
        {             
            // Products is a JSON representation of the returned rows. 
            // Serialize the input in the desired format.
            var serializedProducts = new List<object>();
            foreach (var product in products)
            {
                var serializedProduct = new
                {
                    ProductId = product.ProductId,
                    clothes = new
                    {
                        Name = product.Name,
                        Cost = product.Cost
                    }
                };
                serializedProducts.Add(serializedProduct);
            }
            var json = JsonConvert.SerializeObject(serializedProducts);
            return new OkObjectResult(json);         
        }     
    } 

    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public decimal Cost { get; set; }
    }
}