querybuilder
querybuilder copied to clipboard
SQL Server use FOR FOR JSON AUTO`
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
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.
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 calledQuery
. - I've chosen the name
Plus
here asInclude
is already used the theSqlKata.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);
}
}
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 :)
Interesting stuff here, it seems, System.Text.Json is not mature enough, will keep this open for now, to collect more feedback