You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 4
Next »
PostgreSQL offers two types for storing JSON data: json and jsonb. To implement efficient query mechanisms for these data types.
The 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
(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;
..........
}
{
"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"
]
}
}
}
}
@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;
}
@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).
