.NET / EF Core / AutoMapper causing PostgreSQL "invalid input syntax for type json" error when saving entity

Question

I'm working on a .NET 8 project using Microsoft.EntityFrameworkCore 8.0.7 with PostgreSQL as the database. I have a table that contains a jsonb column, and I'm using AutoMapper to map a DTO to the entity.

Here is my entity:

public partial class PickingAlgorithmConfig
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public string? RuleJson { get; set; }  // jsonb column in PostgreSQL
    public int WarehouseId { get; set; }
    
    // Other properties omitted for brevity
}

Here is my DTO:

public class PickingAlgorithmConfigUpsertRequest
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int WarehouseId { get; set; }
    public string? RuleJson { get; set; } // can be empty string or valid JSON
}

I set up AutoMapper like this:

CreateMap<PickingAlgorithmConfigUpsertRequest, PickingAlgorithmConfig>()
    .ForMember(dest => dest.RuleJson,
               opt => opt.MapFrom(src => string.IsNullOrWhiteSpace(src.RuleJson) ? null : src.RuleJson))
    .ReverseMap();

And my service method:

public async Task<ApiResponse<PickingAlgorithmConfigDto>> AddOrUpdateAsync(PickingAlgorithmConfigUpsertRequest upsertRequest, bool isUpdate)
{
    try
    {
        PickingAlgorithmConfig entity;
        var warehouse = await _warehouseRepository.GetByIdAsync(upsertRequest.WarehouseId);

        if (warehouse == null)
        {
            return new ApiResponse<PickingAlgorithmConfigDto>
            {
                Success = false,
                Data = null,
                Message = "Warehouse not found."
            };
        }

        if (isUpdate)
        {
            entity = await _pickingAlgorithmConfigRepository.GetByIdAsync(upsertRequest.Id);
            if (entity == null)
            {
                return new ApiResponse<PickingAlgorithmConfigDto>
                {
                    Success = false,
                    Data = null,
                    Message = "Picking algorithm configuration not found."
                };
            }

            _mapper.Map(upsertRequest, entity);
            await _pickingAlgorithmConfigRepository.UpdateAsync(entity);
        }
        else
        {
            entity = _mapper.Map<PickingAlgorithmConfig>(upsertRequest);
            await _pickingAlgorithmConfigRepository.AddAsync(entity);
        }

        await _pickingAlgorithmConfigRepository.SaveChangesAsync();

        
        var dto = _mapper.Map<PickingAlgorithmConfigDto>(entity);

        return new ApiResponse<PickingAlgorithmConfigDto>
        {
            Success = true,
            Data = dto,
            Message = "Operation success."
        };
    }
    catch (Exception ex)
    {
        return new ApiResponse<PickingAlgorithmConfigDto>
        {
            Success = false,
            Data = null,
            Message = $"Operation failed: {ex.Message}"
        };
    }
}

When I try to add a new record, I sometimes get this PostgreSQL error:

22P02: invalid input syntax for type json

I want AutoMapper to set RuleJson to null if the incoming string is empty or not valid JSON, to avoid this error.

I've seen some questions about PostgreSQL JSON errors, but they don't involve AutoMapper mapping from a DTO.

My question: How can I configure AutoMapper (or handle this in the service) so that RuleJson is null whenever the input string is empty or invalid JSON, and thus prevent the PostgreSQL invalid input syntax for type json error?

Charlieface · Accepted Answer · 2025-10-20 00:58:46Z

4

You could use the following function to validate JSON.

private bool IsValidJson(string? json)
{
    if (string.IsNullOrWhiteSpace(json))
        return false;

    var utf8 = Encoding.UTF8.GetBytes(json);
    var utf8Reader = new Utf8JsonReader(utf8);
    if (!JsonDocument.TryParseValue(utf8Reader, out var doc))
        return false;

    doc?.Dispose();
    return true;
}

Then

    .ForMember(dest => dest.RuleJson,
               opt => opt.MapFrom(src => IsValidJson(src.RuleJson) ? src.RuleJson : null))

If you already have a JsonDocument then you should pass that instead, rather than stringifying it, as noted in the documentation.

Note that in your model you should specify the column type for jsonb

    [Column(TypeName = "jsonb")]
    public string? RuleJson { get; set; }  // jsonb column in PostgreSQL

answered Oct 20 at 0:58

Charlieface

78.4k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Антон Oct 21 at 22:27

I want to add single comment. You have to decorate your jsonb column with type attribute and you mihgt have to add EnableDynamicJson() to your connection configuration. Depending on the version of EF it could help PostgresSql to work with jsonb types. I had experience migration grom EF core 6 to 9 when all jsonb columns stopped working and this line saved the day.

Collectives™ on Stack Overflow

.NET / EF Core / AutoMapper causing PostgreSQL "invalid input syntax for type json" error when saving entity

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related