The temporal type tjsonb allows to represent the evolution in time of jsonb values. As all temporal types, it comes in three subtypes, namely, instant, sequence, and sequence set. Examples of tjsonb values in these subtypes are given next.
-- Instant
SELECT tjsonb '"{\"vehicleId\": 1, \"location\": \"Point(1 1)\"}"@2001-01-01';
-- Sequence with discrete interpolation
SELECT tjsonb '{{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
{"vehicleId": 1, "location": "Point(2 2)"}@2001-01-02}';
-- Sequence with step interpolation
SELECT tjsonb '[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
{"vehicleId": 1, "location": "Point(2 2)"}@2001-01-02]';
-- Sequence set
SELECT tjsonb '{[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
{"vehicleId": 1, "location": "Point(2 2)"}@2001-01-02],
[{"vehicleId": 1, "location": "Point(2 2)"}@2001-01-03,
{"vehicleId": 1, "location": "Point(3 3)"}@2001-01-04]}';
As can be seen above, for the Instant subtype we need to enclose the JSONB value between quotes and escape the inner quotes, otherwise the opening brace of the JSONB value would be interpreted as the beginning of a Sequence subtype with discrete interpolation.
The tjsonb type accepts type modifiers (or typmod in PostgreSQL terminology) to specify the subtype. The possible values for the subtype are Instant, Sequence, and SequenceSet. The argument is optional and if not specified for a column, values of any subtype are allowed.
SELECT tjsonb(Instant) '"{\"vehicleId\": 1, \"location\": \"Point(1 1)\"}"@2001-01-01';
-- {"location": "Point(1 1)", "vehicleId": 1}@2001-01-01
SELECT tjsonb(Sequence) '"{\"vehicleId\": 1, \"location\": \"Point(1 1)\"}"@2001-01-01';
-- ERROR: Temporal type (Instant) does not match column type (Sequence)
Temporal JSONB values of sequence or sequence set subtype are converted into a normal form so that equivalent values have identical representations. For this, consecutive instant values are merged when possible. Three consecutive instant values can be merged into two if their JSONB values are equal. Also, two consecutive sequences that are adjacent and have the same end and start JSONB value can be merged into a single one. Examples of transformation into a normal form are as follows.
SELECT asText(tjsonb '[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-02,
{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-03]');
/* [{"location": "Point(1 1)", "vehicleId": 1}@2001-01-01,
{"location": "Point(1 1)", "vehicleId": 1}@2001-01-03] */
SELECT asText(tjsonb '{[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
{"vehicleId": 1, "location": "Point(2 2)"}@2001-01-03],
({"vehicleId": 1, "location": "Point(2 2)"}@2001-01-03,
{"vehicleId": 1, "location": "Point(2 2)"}@2001-01-04]}');
/* {[{"location": "Point(1 1)", "vehicleId": 1}@2001-01-01,
{"location": "Point(2 2)", "vehicleId": 1}@2001-01-02,
{"location": "Point(2 2)", "vehicleId": 1}@2001-01-04]} */