Deserialize postgres jsonb to JObject

[anujb]

I have a table that has a jsonb column which I'd like to deserialize to a JObject (Newtonsoft.Json) or the equivalent API in System.Text.Json.

I am following this old issue which uses a SqlMapper:

https://github.com/DapperLib/Dapper/issues/719

But I get the following error:

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
      Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "["
      
      POSITION: 15
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult()
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
         at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1066
         at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1094
         at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
         at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
         at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 734
         at Dapper.Database`1.Query[T](String sql, Object param, Boolean buffered) in /_/Dapper.Rainbow/Database.cs:line 360
         at Dapper.Database`1.Table`2.All() in /_/Dapper.Rainbow/Database.cs:line 127
         at Program.<>c.<<Main>$>b__0_0()
         at lambda_method1(Closure , Object , HttpContext )
         at Microsoft.AspNetCore.Http.RequestDelegateFactory.<>c__DisplayClass36_0.<Create>b__0(HttpContext httpContext)
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
        Exception data:
          Severity: ERROR
          SqlState: 42601
          MessageText: syntax error at or near "["
          Position: 15
          File: scan.l
          Line: 1180
          Routine: scanner_yyerror

[mgravell]

The message suggests a syntax error in the command. Can we see the command you're issuing here, including how you're handling parameters?

[anujb]

@mgravell apologies I pasted the wrong error. Here is the updated error:

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.Data.DataException: Error parsing column 1 (data={"greeting": "Welcome to quicktype!", "instructions": ["Type or paste JSON here", "Or choose a sample above", "quicktype will generate code in your", "chosen language to parse the sample data"]} - String)
       ---> System.InvalidCastException: Invalid cast from 'System.String' to 'System.Text.Json.JsonDocument'.
         at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
         at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider)
         at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
         at Deserialize00749392-c95e-4b01-91c7-f9b39cacf6f9(IDataReader )
         --- End of inner exception stack trace ---
         at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in /_/Dapper/SqlMapper.cs:line 3706
         at Deserialize00749392-c95e-4b01-91c7-f9b39cacf6f9(IDataReader )
         at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext()
         at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
         at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
         at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
         at Dapper.Database`1.Query[T](String sql, Object param, Boolean buffered)
         at Program.<>c.<<Main>$>b__0_0()
         at lambda_method1(Closure , Object , HttpContext )
         at Microsoft.AspNetCore.Http.RequestDelegateFactory.<>c__DisplayClass36_0.<Create>b__0(HttpContext httpContext)
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

[mgravell]

You could presumably create an ITypeHandler for this; here's how we do this for XmlDocument, XDocument and XElement: https://github.com/DapperLib/Dapper/blob/main/Dapper/XmlHandlers.cs

You would then call SqlMapper.AddTypeHandler to register your handler.

Note: we can't ship this "in the box" without adding additional dependencies (for example on Json.NET) - which we don't want to do.

[anujb]

Thank you. I discovered that Npgsql supports Json.NET's JObject Type. I just had to write a custom SqlMapper that first converted it to a string and then deserialized it.

Documenting this here for others and closing out the comment.

Feel free to comment back if there's more efficient way of doing this than first doing the string conversion.

public class JObjectHandler : TypeHandler<JObject>
{
    private JObjectHandler() { }
    public static JObjectHandler Instance { get; } = new JObjectHandler();
    public override JObject Parse(object value)
    {
        var json = value.ToString();
        return json == null ? null : JObject.Parse(json);
    }
    public override void SetValue(IDbDataParameter parameter, JObject value)
    {	
        parameter.Value = value?.ToString(Newtonsoft.Json.Formatting.None);
        ((NpgsqlParameter)parameter).NpgsqlDbType = NpgsqlDbType.Jsonb;
    }
}