spring boot map json.md

PostgreSQL Map Storage Strategies

Created: 7/23/2025 12:46
Updated: 7/23/2025 12:47
Exported: 7/23/2025 12:48
Link: https://claude.ai/chat/63be5a25-1e26-4592-a0b9-f9bf9aac3fde

Response:

Here are several ways to handle Map<key,value> storage in PostgreSQL with Spring Boot:

1. Using JPA with JSONB (Recommended)

Entity with @JdbcTypeCode:

@Entity
@Table(name = "my_entity")
public class MyEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @JdbcTypeCode(SqlTypes.JSON)
    @Column(columnDefinition = "jsonb")
    private Map<String, Object> properties;
    
    // constructors, getters, setters
}

Repository with JSONB queries:

@Repository
public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
    
    @Query(value = "SELECT * FROM my_entity WHERE properties->>'key1' = :value", 
           nativeQuery = true)
    List<MyEntity> findByPropertyValue(@Param("value") String value);
    
    @Query(value = "SELECT * FROM my_entity WHERE properties ? :key", 
           nativeQuery = true)
    List<MyEntity> findByPropertyKey(@Param("key") String key);
    
    @Query(value = "SELECT * FROM my_entity WHERE properties @> :json::jsonb", 
           nativeQuery = true)
    List<MyEntity> findByJsonContains(@Param("json") String json);
}

2. Using @ElementCollection

For simple String key-value pairs:

@Entity
public class MyEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @ElementCollection
    @CollectionTable(name = "entity_properties", 
                    joinColumns = @JoinColumn(name = "entity_id"))
    @MapKeyColumn(name = "property_key")
    @Column(name = "property_value")
    private Map<String, String> properties = new HashMap<>();
    
    // getters, setters
}

3. Custom Converter Approach

JSON Converter:

@Converter
public class MapToJsonConverter implements AttributeConverter<Map<String, Object>, String> {
    
    private final ObjectMapper objectMapper = new ObjectMapper();
    
    @Override
    public String convertToDatabaseColumn(Map<String, Object> attribute) {
        try {
            return objectMapper.writeValueAsString(attribute);
        } catch (JsonProcessingException e) {
            throw new RuntimeException("Error converting map to JSON", e);
        }
    }
    
    @Override
    public Map<String, Object> convertToEntityAttribute(String dbData) {
        try {
            return objectMapper.readValue(dbData, 
                new TypeReference<Map<String, Object>>() {});
        } catch (JsonProcessingException e) {
            throw new RuntimeException("Error converting JSON to map", e);
        }
    }
}

Using the converter:

@Entity
public class MyEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Convert(converter = MapToJsonConverter.class)
    @Column(columnDefinition = "TEXT")
    private Map<String, Object> properties;
}

4. Service Layer Examples

Service for CRUD operations:

@Service
@Transactional
public class MyEntityService {
    
    @Autowired
    private MyEntityRepository repository;
    
    public MyEntity saveWithProperties(Map<String, Object> properties) {
        MyEntity entity = new MyEntity();
        entity.setProperties(properties);
        return repository.save(entity);
    }
    
    public void updateProperty(Long id, String key, Object value) {
        MyEntity entity = repository.findById(id)
            .orElseThrow(() -> new EntityNotFoundException("Entity not found"));
        
        if (entity.getProperties() == null) {
            entity.setProperties(new HashMap<>());
        }
        entity.getProperties().put(key, value);
        repository.save(entity);
    }
    
    public Object getProperty(Long id, String key) {
        return repository.findById(id)
            .map(entity -> entity.getProperties())
            .map(props -> props.get(key))
            .orElse(null);
    }
    
    public List<MyEntity> findEntitiesWithProperty(String key, String value) {
        return repository.findByPropertyValue(value);
    }
}

5. Configuration

Add to application.yml:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: user
    password: password
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true

6. Advanced JSONB Operations

Complex queries with Criteria API:

@Repository
public class MyEntityCustomRepository {
    
    @PersistenceContext
    private EntityManager entityManager;
    
    public List<MyEntity> findByComplexJsonQuery(Map<String, Object> searchCriteria) {
        StringBuilder queryBuilder = new StringBuilder("SELECT e FROM MyEntity e WHERE ");
        
        searchCriteria.forEach((key, value) -> {
            queryBuilder.append("JSON_EXTRACT(e.properties, '$.").append(key)
                       .append("') = :").append(key).append(" AND ");
        });
        
        String query = queryBuilder.toString().replaceAll(" AND $", "");
        
        Query q = entityManager.createQuery(query);
        searchCriteria.forEach(q::setParameter);
        
        return q.getResultList();
    }
}

Best Practices

1. Use JSONB over JSON - better performance and indexing
2. Add indexes for frequently queried JSON keys:

   CREATE INDEX idx_properties_key1 ON my_entity USING GIN ((properties->>'key1'));

3. Validate JSON structure in your service layer
4. Consider using @Transactional for consistency when updating maps

The JSONB approach with @JdbcTypeCode is usually the best choice for Spring Boot applications as it provides good performance, flexibility, and native PostgreSQL JSON query capabilities.

---

Powered by Claude Exporter