Convert Dapper raw SQL Result to Nested JSON Array

I have developed a web api in .Net Core 5 which uses dapper to run a tabled valued function and return the SQL results. These results are then used to fill various select boxes on the front end in VueJS. However, when I began to build out my front end more I realized my JSON arrays could be nested to really help reduce the number of requests I make to the server as my select boxes are dependent. For instance, one select box includes states and then next select box relates to the cities in those states. Adjusting the tabled value function to return a single table was easy by adding a innerjoin between my state table in the database and the cities table. The joining key was a field called STATE_ID. Therefore I just have multiple rows due to multiple cities per state. So now what I am trying to figure out is how to take this result in my web api and my table valued function result without the use of models into a nested json array such that my results are as follows:

[{state: 'Maryland', cities :[{city: 'Baltimore'}, {city: 'Harford County'}]} , 
{state: 'Pennsylvania', cities :[{city: 'York'}, {city: 'Fawn Grove'}]}]

Table valued function result from A2Q00001_StateInfo(USERNUMBER):

|      State_ID       |     State_Name   |      City_Name      |  
|---------------------|------------------|---------------------|
|          1          |      Maryland    |    Baltimore        |
|          1          |      Maryland    | Harford County      |
|          2          |  Pennsylvania    |    York             |
|          2          |  Pennsylvania    | Fawn Grove          |

My controller is as follows:

        public ActionResult StateAndCities([FromQuery] String USERNUMBER)
        {
 
            //We have parameters here just in case we want to use them
            IEnumerable queryResult;
            String query = "select * from dbo.A2Q00001_StateInfo(@USERNUMBER);";

            using (var connection = new SqlConnection(connectionString))
            {
                queryResult = connection.Query(query, new { USERNUMBER = USERNUMBER });
            }

            return Ok(queryResult);

        }

All of the tutorials I have seen online use models to create the nested JSON object and return it however I am not sure how to create the nested object using the serialization in the Ok() function in asp.net core. Is this even posssible or do I need to perform operations on the queryResult from the dapper query? Any point in the right direction would be great.

Solution

My advice: split this into steps. I'm guessing your A2Q00001_StateInfo UDF here returns a State and City column (edit: I was close, it was State_Name, via the edit), among other things. So first step: let's just read that:

class SomeType
{
    public string State_Name { get; set; }
    public string City { get; set; }
}
//...
var queryResult = connection.Query<SomeType>(
    "select State_Name, City from dbo.A2Q00001_StateInfo(@USERNUMBER);",
    new { USERNUMBER }).AsList();

This gets our data from the database into local memory. Note that I filtered out irrelevant columns to reduce overheads.

Now, the next step is to structure that data - it looks like you want to aggregate by state, and create an array of the cities in each; so: let's do that:

var structured =
    from grp in queryResult.GroupBy(x => x.State_Name)
    select new
    {
        state = grp.Key,
        cities = grp.Select(row => new { city = row.City }).ToArray()
    };

This gives us a projection (using anonymous types) that does the restructuring we want. Finally, we need to convert it to JSON; this might be as simple as:

return Ok(structured);

Or you might need to use the Json/JsonResult APIs directly. However, now that the data is structured: any JSON serializer should know what we want to do here.

Note: you probably can rewrite all this into a single expression, but: don't do that; you're not trying to impress the compiler - it won't care either way. Make the code clear and obvious for the next person who is going to need to touch it (which might well be you).