PostgreSQL JSONB Array not mapped correctly

[szv]

Is there an existing issue for this?

• I have searched the existing issues

Describe the bug

An array with the PostgreSQL-JSONB-Datatype does not get mapped correctly.

Further details are shown below...

Steps to reproduce

1. I have the following Entity-Classes:

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string? Description { get; set; }
    // configured with JSONB-Datatype
    public List<Tag> Tags { get; set; } = new List<Tag>();
    // configured with JSONB-Datatype
    public CompanyDetails Details { get; set; } = new CompanyDetails();
}

public class Tag
{
    public string Name { get; set; } = string.Empty;
}

public class CompanyDetails
{
    public string Detail1 { get; set; } = string.Empty;

    public string Detail2 { get; set; } = string.Empty;

    public string Detail3 { get; set; } = string.Empty;
}

As you can see, the class Company contains a list of Tags, and Company_Details which both are stored as JSONB within the database.

When I run the following GraphQL-Query:

companies {
  id
  name
  details {
    detail1
    detail2
    detail3
  }
}

... I get the following output ...

{
  "data": {
    "companies": [
      {
        "id": 1,
        "name": "Company 1",
        "details": {
          "detail1": "Content1",
          "detail2": "Content2",
          "detail3": "Content3"
        }
      }
    ]
  }
}

... which is the expexted result.
The corresponding mapped SQL-statement shows, that the mapping to the JSONB-DB-Type works as expected;

SELECT c.id, c.name, c.details IS NOT NULL, c.details->>'Detail1', c.details->>'Detail2', c.details->>'Detail3'
FROM companies AS c

Now the problem

When running the following GraphQL-Query:

companies {
  id
  name
  details {
    detail1
    detail2
    detail3
  }
  tags {     # added
    name     # added
  }          # added
}

... I get the following error ...

{
  "errors": [
    {
      "message": "Unexpected Execution Error",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "companies"
      ],
      "extensions": {
        "message": "The LINQ expression 'p1 => new Tag{ Name = p1.Name }\r\n' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.",
        "stackTrace": "   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression)\r\n   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCall)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCall)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)\r\n   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()\r\n   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)\r\n   at HotChocolate.Data.ToListMiddleware`1.InvokeAsync(IMiddlewareContext context)\r\n   at HotChocolate.Types.EntityFrameworkObjectFieldDescriptorExtensions.<>c__DisplayClass2_1`1.<<UseDbContext>b__4>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Types.EntityFrameworkObjectFieldDescriptorExtensions.<>c__DisplayClass2_1`1.<<UseDbContext>b__4>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)"
      }
    }
  ]
}

When adding the [UseProjection]-Attribute to the Tags-Property of the Company-class, I get the following result:

{
  "data": {
    "companies": [
      {
        "id": 1,
        "name": "Company 1",
        "details": {
          "detail1": "Content1",
          "detail2": "Content2",
          "detail3": "Content3"
        },
        "tags": []
      }
    ]
  }
}

... although the content of the tags-column in the database is:

[{ "Name": "Name hier" }]

As you can see in the corresponding SQL-statement the tags-column does not get selected:

SELECT c.id, c.name, c.details IS NOT NULL, c.details->>'Detail1', c.details->>'Detail2', c.details->>'Detail3'
FROM companies AS c

Do you have any ideas how to make this work or is it a bug of HC?

Kind regards,
Sebastian

Relevant log output

No response

Additional Context?

I use

• ASP.NET 6
• EFCore 6.0
• Npgsql 6.0
• HC 12.2.1
• PostgreSQL 12

Product

Hot Chocolate

Version

12.2.1

[avisra]

I just ran into this same error. Is there a workaround? @michaelstaib

[avisra]

I managed to solve this by preventing projections on inner fields of jsonb types:
`public static class JsonBProjectionProviderDescriptorQueryableExtensions
{
public static IProjectionProviderDescriptor AddJsonBHandlers(
this IProjectionProviderDescriptor descriptor) =>
descriptor.RegisterFieldHandler();
}

public class QueryableJsonBProjectionScalarHandler
    : QueryableProjectionScalarHandler
{
    public override bool CanHandle(ISelection selection)
    {
        var isJsonType = selection.Field.Member?.GetCustomAttributes(true)?.Where(a => a is ColumnAttribute)?.Cast<ColumnAttribute>()?.Any(a => a.TypeName == "jsonb") ?? false;
        return selection.Field.Member is not null &&
        isJsonType;
    }
}

public static class JsonBProjectionsRequestExecutorBuilderExtensions
{
    public static IRequestExecutorBuilder AddJsonBProjections(
        this IRequestExecutorBuilder builder)
    {
        if (builder is null)
        {
            throw new ArgumentNullException(nameof(builder));
        }

        return builder.ConfigureSchema(x => x.AddJsonBProjections());
    }
}

public static class JsonBProjectionsSchemaBuilderExtensions
{
    public static ISchemaBuilder AddJsonBProjections(this ISchemaBuilder builder)
    {
        if (builder is null)
        {
            throw new ArgumentNullException(nameof(builder));
        }

        return builder.AddConvention<IProjectionConvention>(
            new ProjectionConventionExtension(
                x => x.AddProviderExtension(
                    new ProjectionProviderExtension(y => y.AddJsonBHandlers()))));
    }
}`

[avisra]

It might be nice, for full postgres/ef core support, to have HotChocolate handle this automatically. I haven't contributed anything to HC before.. but I'll see what I can whip up.

[michaelstaib]

@avisra if you want to contribute on this we would appreciate this... get something started and we can fill in the blanks that you have.

[FlayaN]

FYI this is also an issue when using ef core 7 json (specifically arrays inside the json column). The solution posted above (#4429 (comment)) works until you have it as part of an union and resolving the entity containing the json column twice, once with the array.

I'll try to create a minimal repro repo later on if it's needed.

[ShipkaChalk]

Thank you @avisra !

[blanks88]

I managed to solve this by preventing projections on inner fields of jsonb types:

public static class JsonBProjectionProviderDescriptorQueryableExtensions { public static IProjectionProviderDescriptor AddJsonBHandlers( this IProjectionProviderDescriptor descriptor) => descriptor.RegisterFieldHandler(); }
public class QueryableJsonBProjectionScalarHandler
    : QueryableProjectionScalarHandler
{
    public override bool CanHandle(ISelection selection)
    {
        var isJsonType = selection.Field.Member?.GetCustomAttributes(true)?.Where(a => a is ColumnAttribute)?.Cast<ColumnAttribute>()?.Any(a => a.TypeName == "jsonb") ?? false;
        return selection.Field.Member is not null &&
        isJsonType;
    }
}

public static class JsonBProjectionsRequestExecutorBuilderExtensions
{
    public static IRequestExecutorBuilder AddJsonBProjections(
        this IRequestExecutorBuilder builder)
    {
        if (builder is null)
        {
            throw new ArgumentNullException(nameof(builder));
        }

        return builder.ConfigureSchema(x => x.AddJsonBProjections());
    }
}

public static class JsonBProjectionsSchemaBuilderExtensions
{
    public static ISchemaBuilder AddJsonBProjections(this ISchemaBuilder builder)
    {
        if (builder is null)
        {
            throw new ArgumentNullException(nameof(builder));
        }

        return builder.AddConvention<IProjectionConvention>(
            new ProjectionConventionExtension(
                x => x.AddProviderExtension(
                    new ProjectionProviderExtension(y => y.AddJsonBHandlers()))));
    }
}`

Thank you so much it works like a charm!