SQL Server use FOR FOR JSON AUTO`

[StephaneMartignier]

Hi everyone

So we are using the SQL builder to create query from OData, and we want to use the FOR AUTO JSON to have results directly wrapped in a correct JSON. To do that we have to use subqueries

For example the following OData "odata/contacts?$expand=Addresses,Car" should be translated to
SELECT Contacts.*, (SELECT Addresses.* FROM Addresses WHERE Addresses.ContactId = Contacts.Id FOR JSON AUTO) As Addresses, (SELECT Cars.* FROM Cars WHERE Cars.ContactId = Contacts.Id FOR JSON AUTO) As Car FROM Contacts FOR JSON AUTO

I'm able to construct each select requests (I can have the relation from the EDM model of OData) and I know I can use subqueries in SQLKata, but what I don't know is how to add the "FOR JSON AUTO" at the end of each query,

Is there a way to do it?

Thanks for your help

[AndreSteenveld]

I've ran in to a similar problem and little experimenting in the playground resulted in the following:

var compiler = new SqlServerCompiler();

var people = new Query( "Person" );

var contracts = new Query( "Contract" ).WhereColumns( "ContractId", "=", "Person.ContractId" );

var query = people
    .Select( "Person.*" )
    .SelectRaw( $"({compiler.Compile(contracts)} FOR JSON AUTO)");

To clean this up I created an extension method which seems to do the trick

public static class QueryExtensions {

    private static SqlServerCompiler compiler = new SqlServerCompiler();

    public static Query SelectForJsonAuto( this Query @this, Query query )
        => @this.SelectRaw( $"( {compiler.Compile(query)} FOR JSON AUTO )" );

}

Now going through the documentation I think it should technically be possible to create a IncludeAsJson sort of method which would include the result of a separate query as a JSON blob. Assuming the following schema and data:

create table words ( first text, second text, third text );
insert into words values 
  ( 'apple', 'apricot', 'anaconda' ),
  ( 'bee', 'ball', 'boat' ),
  ( 'color', 'cheers', 'cactus' )
  ;

-- The output we're expecting
-- [{"first":"apple","second":"apricot","third":"anaconda"},{"first":"bee","second":"ball","third":"boat"},{"first":"color","second":"cheers","third":"cactus"}]

SQLite is a little verbose as you have to build the entire object yourself:

select 
  json_group_array(  
	json_object( 'first', first, 'second', second, 'third', third ) ) as words 
from words;

Postgres has a neat json_agg function which does the trick:

select json_agg( words ) from words;

I'm sure Oracle, MySQL and other databases will have similar functions.

[AndreSteenveld]

A vacation and some time later, I worked on this a little more. For all intents and purposes this smells like a aggregation (and in PostgreSQL it is) but it seemed nearly impossible to implement it as such cleanly. I've looked at overriding CompileColumn*s* as well but it didn't seem like the correct solution.

To get it all to work though I've created a JsonColumn type which is essentially the same as the QueryColumn and only serves as a mark to the compiler. Every compiler can implement a custom method of serializing a result to a json blob. Not really ideal but it works. For now I've written out JsonSqlServerCompiler and JsonPostgresCompiler as these are straight forward enough. I'm sure with the correct massaging of the supplied query object JsonSqliteCompuler would be pretty straight forward to implement, although the resulting query might not be very pretty.

using SqlKata;
using SqlKata.Compilers;
using SqlKata.Extensions;
using SqlQuery = SqlKata.Query;

namespace Database
{

    public class JsonColumn : QueryColumn
    {
        public override AbstractClause Clone()
        {
            return new JsonColumn
            {
                Engine = Engine,
                Query = Query.Clone(),
                Component = Component,
            };
        }
    }

    public class JsonPostgresCompiler : PostgresCompiler
    {
        public override string CompileColumn(SqlResult ctx, AbstractColumn column)
        {
            return column switch
            {
                JsonColumn c => CompileJsonColumn(ctx, c),
                _ => base.CompileColumn(ctx, column)
            };

            string CompileJsonColumn(SqlResult ctx, JsonColumn column)
            {

                var alias = "";

                if (!string.IsNullOrWhiteSpace(column.Query.QueryAlias))
                {
                    alias = $" {ColumnAsKeyword}{WrapValue(column.Query.QueryAlias)}";
                }

                var subCtx = CompileSelectQuery(column.Query);

                ctx.Bindings.AddRange(subCtx.Bindings);

                return "json_agg(" + subCtx.RawSql + $"){alias}";

            }

        }
    }

    public class JsonSqlServerCompiler : SqlServerCompiler
    {

        public override string CompileColumn(SqlResult ctx, AbstractColumn column)
        {
            return column switch
            {
                JsonColumn c => CompileJsonColumn(ctx, c),
                _ => base.CompileColumn(ctx, column)
            };

            string CompileJsonColumn(SqlResult ctx, JsonColumn column)
            {

                var alias = "";

                if (!string.IsNullOrWhiteSpace(column.Query.QueryAlias))
                {
                    alias = $" {ColumnAsKeyword}{WrapValue(column.Query.QueryAlias)}";
                }

                var subCtx = CompileSelectQuery(column.Query);

                ctx.Bindings.AddRange(subCtx.Bindings);

                return "(" + subCtx.RawSql + $" FOR JSON AUTO){alias}";

            }

        }

    }

    public static class PlusQueryExtensions
    {

        public static SqlQuery Plus(this SqlQuery @this, SqlQuery query, string alias)
        {
            @this.Method = "select";
            
            @this.AddComponent("select", new JsonColumn
            {
                Query = query.Clone().As(alias)
            });

            return @this;
        }

        public static SqlQuery Plus(this SqlQuery @this, Func<SqlQuery, SqlQuery> callback, string alias)
            => @this.Plus(callback(@this.NewChild()), alias);

    }
}

• The SqlQuery alias is because it is included in a project which already has a type called Query.
• I've chosen the name Plus here as Include is already used the the SqlKata.Execute and this is a way to work around the N+1 problem

Edit: I've created a little sanity test for SQL server to make sure it works and have included two fixes in the orginal listing (override the CompileColumn method and implement the Clone method on the JsonColumn class.

//
// The sanity test for SQL server 
//
public class Plus
{
    [Fact]
    public void inlcude_subquery_as_json_blob()
    {
        const string expected =
            "SELECT [A].*, (SELECT [B].* FROM [B] WHERE [A].[AId] = [B].[BId] FOR JSON AUTO) AS [Blob] FROM [A]";

        var compiler = new JsonSqlServerCompiler();

        var bQuery = (new SqlQuery())
            .Select("B.*")
            .From("B")
            .WhereColumns("A.AId", "=", "B.BId");

        var aQuery = (new SqlQuery())
            .Select("A.*")
            .From("A")
            .Plus(bQuery, "Blob");

        var sql = compiler.Compile(aQuery).RawSql;

        Assert.Equal(expected, sql);
    }
}

[AndreSteenveld]

Working on this some more I've created a little overload of the Dapper Query<T> method which will extract the extra mappings from the supplied query. The thing I'm now running in to is making sure System.Text.Json can map everything over. Also this code makes the big assumption that all models are [Record] types from LanguageExt project.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using SqlKata.Compilers;
using SqlQuery = SqlKata.Query;

using Dapper;

namespace Database
{
    public static class SqlKata
    {
        public static void RegisterCompiler(Compiler compiler) => SqlKata.compiler = compiler;

        private static Compiler compiler;

        private static (string type, string member) SplitPath(string p)
        {
            var path = p.Split('.');

            return (
                String.Join('.', path.SkipLast(1)),
                path.Last()
            );
        }
     
        private static Func<object[], TReturn> Map<TReturn>(string[] paths) => objects =>
        {

            //
            // We know a few things here:
            //      1) The first element is TReturn, this is the element we want to fold in to
            //      2) All models are a [Record] from the LanguageExt package
            //      3) These are provided as a JSON blob we only have to deserialize
            //      4) The max level we're going to look in to is the level of the result, the JSON deserizalizer will have to deal with nested blobs
            //

            IEnumerable<(string path, object value)> tuples = paths.Zip(objects);
            var record = (TReturn) tuples.First().value;

            var named = tuples
                .Skip(1)
                .Map(t =>
                {
                    //
                    // As we'll eventually start looking to squeeze out the best performance I think most of the time is going to be spent
                    // deserializing the object. I am not worried about optimizing this to the max.
                    //
                    var tuple = ((string path, string blob)) t;
                    var path = SplitPath(tuple.path);

                    // There might be some win here, after this line memory usage peaks to ~90mb, I am not sure if this is coincidental
                    // or actually has anything to do with this line.
                    var type = Enumerable.Single(

                        from assembly in AppDomain.CurrentDomain.GetAssemblies()
                        from info in assembly.DefinedTypes
                        where path.type == info.FullName
                        select info

                    );

                   var member = type.GetMember(path.member).Single() switch
                    {
                        FieldInfo info => info.FieldType,
                        PropertyInfo info => info.PropertyType,
                        _ => throw new NotSupportedException($"Can't assign to [ {tuple.path} ]")
                    };

                    var value = System.Text.Json.JsonSerializer.Deserialize(tuple.blob, member);

                    return (path.member, value);

                })
                .ToLookup(
                    t => t.member,
                    t => t.value
                );

            //
            // Reading the runtime code that goes in to this I am pretty sure if there is a win to be gained it would be here. It
            // seems that if we could create a delegate for the with method and memoize that somewhere we can invoke it alot faster
            // we'll have to figure something out for the variables though...
            //
            var with = typeof(TReturn).GetMethod("With");

            var arguments = with
                .GetParameters()
                .Map(p => named.Contains(p.Name) ? named[p.Name] : null)
                .ToArray();

            return (TReturn) with.Invoke(record, arguments);

        };


        public static IEnumerable<TReturn> Query<TReturn>(this IDbConnection @this, SqlQuery sql, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            // You'd think this would work but it doesn't... It thows a cast exception somewhere inside SqlKata
            //var columns = sql.GetComponents<JsonColumn>("select", compiler.EngineCode);

            var columns = 
                from component in sql.GetComponents("select", compiler.EngineCode)
                where component is JsonColumn
                select (JsonColumn)component;

            var compiled = compiler.Compile(sql);

            return @this.Query(

                sql: compiled.Sql,

                types: new[] {typeof(TReturn)}
                    .Concat(from _ in columns select typeof(string))
                    .ToArray(),

                map: Map<TReturn>(
                    new[] {typeof(TReturn).FullName}
                        .Concat(from c in columns select c.Query.QueryAlias)
                        .ToArray()
                ),

                param: compiled.NamedBindings,
                transaction,
                buffered,

                splitOn: String.Join(",", from c in columns select c.Query.QueryAlias),

                commandTimeout,
                commandType

            );

        }
    }
}

Edit: For all future readers, as mentioned I've been working with LanguageExt and System.Text.Json seems to have issues de-serializing types that implement IEnumerable<T> that are not collections. (#46920). I'm working on converting this to use Newtonsoft but that will be left as an exercise to the student :)

[ahmad-moussawi]

Interesting stuff here, it seems, System.Text.Json is not mature enough, will keep this open for now, to collect more feedback