Introduction
The goal of this document is to investigate about the PostgreSQL capability to handle Json documents.
PostgreSQL offers two types for storing JSON data: json
and jsonb. T
he json
data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb
data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb
also supports indexing, which can be a significant advantage.
Because the json
type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb
does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.
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.
The Java code shown below, is the model used by Rest Api Controller. (Validations and Swagger annotations have been removed for simplicity)
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;
..........
}
The Json shown below is an example using the model shown before.
{
"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"
]
}
}
}
}
To store data on PostgreSQL we still have to use Entities, but part of Json model could be stored as jsonb type. In this example we can use just one class as persistence model as shown below.
@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;
}
Jakarta and Spring do no support json Type, but we can use Converters to convert a json model to a String. The more generic is a converter, more we can reuse it.
@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
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();
}
@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());
}
}
}
This example is available here (https://gerrit.nordix.org/c/local/onap/clamp-pilot/+/11966).
Use case scenarios
- The principal model used by Business logic is the Json model
- Collection of values: Map of String as example