Page History

Introduction

The goal of this document is to investigate about the PostgreSQL capability to handle Json documents.

...

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys. (https://www.postgresql.org/docs/current/datatype-json.html)

Demo Application

To better understand these functionality, it has been created a Demo Application. That it is available here (https://gerrit.nordix.org/c/local/onap/clamp-pilot/+/11966).

Data Transfer Object

The Java code shown below, is the model data transfer Object used by Rest Api Controller. (Validations and Swagger annotations have been removed for simplicity).

Code Block

language	java
title	Model
collapse	true

public class ElementDto {

    private String name;

    private List<String> tags;

..........
}

public class DocumentDto {

    private String name;

    private Map<String, ElementDto> elements;

..........
}

public class ItemDto {
    private String key;

    private String text;

    private Map<String, String> properties;

    private DocumentDto document;
    
..........
}

...

Code Block

language	js
title	Json Document
collapse	true

{
  "key": "1",
  "text": "This is an example",
  "properties": {
    "additionalProp1": "Val 10",
    "additionalProp2": "11",
    "additionalProp3": "12"
  },
  "document": {
    "name": "DocumentExample",
    "elements": {
      "additionalProp1": {
        "name": "ElementExample 1",
        "tags": [
          "tag1"
        ]
      },
      "additionalProp2": {
        "name": "ElementExample 2",
        "tags": [
          "tag2"
        ]
      },
      "additionalProp3": {
        "name": "ElementExample 3",
        "tags": [
          "tag3"
        ]
      }
    }
  }
}

Domain model

To store data on PostgreSQL we still have to use Entities, but part of Json model Dto classes could be stored as jsonb type or at least we can create new domain classes that are equal to those DTO classes.

In this example we can use just one class as persistence model as shown below.

Code Block

language	java
title	Persistence Model
collapse	true

@Entity
@Data
public class Item implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    private String key;

    @Column(name = "text", nullable = false)
    private String text;

    @Convert(converter = StringToMapConverter.class)
    @Column(columnDefinition = "jsonb", nullable = false)
    private Map<String, String> properties;

    @Convert(converter = StringToDocumentConverter.class)
    @Column(columnDefinition = "jsonb", nullable = false)
    @NotNull
    private DocumentDto document;
}

Converters

Jakarta and Spring do no support json Type, but we can use Converters to convert a json model an Object to a String. The more generic is a converter, more we can reuse it.

Code Block

language	java
title	Converters
collapse	true

@Slf4j
@Converter(autoApply = true)
public final class StringToMapConverter implements AttributeConverter<Map<String, ? extends Object>, String> {

    private static final ObjectMapper MAPPER = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(Map<String, ? extends Object> map) {
        try {
            return map == null ? null : MAPPER.writeValueAsString(map);
        } catch (JsonProcessingException ex) {
            throw new IllegalStateException(ex);
        }
    }

    @Override
    public Map<String, ? extends Object> convertToEntityAttribute(String dbData) {
        if (dbData == null) {
            return Map.of();
        }
        try {
            return MAPPER.readValue(dbData, new TypeReference<Map<String, Object>>() {});
        } catch (IOException ex) {
            log.error("Cannot convert String to Map.");
            return Map.of();
        }
    }
}

@Slf4j
@Converter(autoApply = true)
public final class StringToDocumentConverter implements AttributeConverter<DocumentDto, String> {

    private static final ObjectMapper MAPPER = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(DocumentDto document) {
        try {
            return document == null ? null : MAPPER.writeValueAsString(document);
        } catch (JsonProcessingException ex) {
            throw new IllegalStateException(ex);
        }
    }

    @Override
    public DocumentDto convertToEntityAttribute(String dbData) {
        if (dbData == null) {
            return new DocumentDto();
        }
        try {
            return MAPPER.readValue(dbData, DocumentDto.class);
        } catch (IOException ex) {
            log.error("Cannot convert String to Map.");
            return new DocumentDto();
        }
    }
}

Repository

The java code below shows some examples how to use the functionalities offered by PosgreSLQ about Json Type. We can apply where clauses, extract and manipulate data into the Json data. In order to use these functionalities, all queries have to be native (nativeQuery = true).

Code Block

language	java
title	Repository
collapse	true

@Repository
public interface ItemRepository extends JpaRepository<Item, String> {

    @Query(value = " SELECT * FROM item where document['name'] @> :value ", nativeQuery = true)
    Stream<Item> findByName(@Param("value") String value);

    @Modifying
    @Query(value = " UPDATE item SET document['name']=:value where key=:key ", nativeQuery = true)
    void updateNameByKey(@Param("key") String key, @Param("value") String value);

    @Query(
            value = " SELECT Cast(jsonb_path_query_array(document['elements'], '$[*].keyvalue().value.tags') as varchar) FROM item ",
            nativeQuery = true)
    Stream<String> findTags();
}

Unit Tests

In order to make unit tests, we can not use H2 due of the incompatibility. Spring supports Testcontainers where we can configure a PostgreSQL Container that will up and running during tests.

Code Block

language	java
title	Unit Test
collapse	true

@Testcontainers
@ExtendWith(SpringExtension.class)
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@ContextConfiguration(
        initializers = DemoApplicationTests.Initializer.class,
        classes = DemoApplication.class)
@TestPropertySource(locations = {"classpath:application_test.properties"})
class DemoApplicationTests {

    @Test
    void contextLoads() {
    }

    @Container
    public static PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer("postgres:latest")
            .withDatabaseName("db_demo").withUsername("sa").withPassword("sa");

    @Configuration
    static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {
        @Override
        public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
            TestPropertyValues
                    .of("spring.datasource.url=" + postgreSQLContainer.getJdbcUrl(),
                            "spring.datasource.username=" + postgreSQLContainer.getUsername(),
                            "spring.datasource.password=" + postgreSQLContainer.getPassword())
                    .applyTo(configurableApplicationContext.getEnvironment());
        }
    }

}

Use case scenarios

The principal model used by Business logic is the Json model
Collection of values: Map of String as example

Item Table

Table of comparison

Type	DataStore	Validation	Query	Index
longtext	MariaDB	SELECT	Yes	No
longtext	PostgreSQL	No	No	No
json	PostgreSQL	INSERT / UPDATE	Yes	No
jsonb	PostgreSQL	INSERT / UPDATE	Yes	Yes

Conclusion

replace all ElementCollections with Converters (Minimum change with Minimum improvement):
- A lot of small Jsons
- Using longtext, we are be able to maintain compatibility with MariaDB
- Minimum Java code impact
- 50% of tables (as _PROPERTIES, _META ) will be removed
ToscaServiceTemplace as Json String:
- One big Json
- Using jsonb only PostgreSQL, will supported
- Medium Java code impact: all persistence classes could be removed or have to be equal to original model
- (90% of tables) will be removed
Cassandra
- Document oriented approach, no converters need
- No support for MariaDB and PostgreSQL
- Huge Java code impact: All repositories and all persistence classes have to change
- Completely different Database

Space shortcuts

Page tree

Versions Compared

Old Version 9

New Version 10

Key

Introduction

Demo Application

Data Transfer Object

Domain model

Converters

Repository

Unit Tests

Use case scenarios

Item Table

Table of comparison

Conclusion