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. 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 data transfer Object used by Rest Api Controller. (Validations and Swagger annotations have been removed for simplicity).
The Json shown below is an example of the model shown before.
Domain model
To store data on PostgreSQL we still have to use Entities, but part of 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.
Converters
Jakarta and Spring do no support json Type, but we can use Converters to convert an Object to a String. The more generic is a converter, more we can reuse it.
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).
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.
Item Table
db_demo=# \d item
Table "public.item"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
key | character varying(255) | | not null |
document | jsonb | | not null |
properties | jsonb | | not null |
text | character varying(255) | | not null |
Indexes:
"item_pkey" PRIMARY KEY, btree (key)
db_demo=# select * from public.item; key | document | properties | text -----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+-------------------- 1 | {"name": "DocumentExample", "elements": {"additionalProp1": {"name": "ElementExample 1", "tags": ["tag1"]}, "additionalProp2": {"name": "ElementExample 2", "tags": ["tag2"]}, "additionalProp3": {"name": "ElementExample 3", "tags": ["tag3"]}}} | {"additionalProp1": "Val 10", "additionalProp2": "11", "additionalProp3": "12"} | This is an example (1 row)
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
My suggestion as shown below is three options:
- 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 (with postfix as _ATTRIBUTES, _PROPERTIES, _METADATA, _OCCURRENCES, _CONSTRAINTS, _TARGETS, _TRIGGERS and _INPUTS) will be removed
- Unit tests not need to change
- Unique Json String:
- One big Json for each model (e.g. saving the whole ToscaServiceTemplace as Json)
- Using jsonb only PostgreSQL, will be 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
- Unit tests need PostgreSQLContainer
- Cassandra
- Document oriented approach full supported by SpringBoot (not needs Converters)
- Huge Java code impact: All repositories and all persistence classes have to change
- No support for MariaDB and PostgreSQL anymore
- Unit tests need an Embedded Cassandra Server (EmbeddedCassandraServerHelper or CassandraContainer)