Devolver la representación en texto bien conocido (WKT)

asText({tjsonb,tjsonb[]}) → {text,text[]}

SELECT asText(tjsonb '[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
  {"vehicleId": 1, "location": "Point(2 2)"}@2001-01-02]');
/* [{"location": "Point(1 1)", "vehicleId": 1}@2001-01-01,
   {"location": "Point(2 2)", "vehicleId": 1}@2001-01-02] */
SELECT asText(ARRAY[tjsonb '"{\"vehicleId\":1, \"location\": \"Point(1 1)\"}"@2001-01-01',
  '"{\"vehicleId\": 1, \"location\": \"Point(2 2)\"}"@2001-01-02']);
/* {"{\"location\": \"Point(1 1)\", \"vehicleId\": 1}@2001-01-01",
   "{\"location\": \"Point(2 2)\", \"vehicleId\": 1}@2001-01-02"} */

Como puede verse arriba, para los arreglos de valores JSONB, los elementos deben encerrarse entre comillas y, por lo tanto, las comillas internas deben escaparse.

Devolver la representación en binario bien conocido (WKB) o en binario bien conocido extendido hexadecimal (HexWKB)

asBinary(tjsonb,endian text='') → bytea

asHexWKB(tjsonb,endian text='') → text

El resultado se codifica utilizando la codificación little-endian (NDR) o big-endian (XDR). Si no se especifica ninguna codificación, se utiliza la codificación de la máquina.

SELECT asBinary(tjsonb '"{\"vehicleId\": 1, \"location\": \"Point(1 2)\"}"@2001-01-01');
-- \x0141000138000000000000000200002008000080090000000a000000090000106c6f636174696f6e7...
SELECT asHexWKB(tjsonb '"{\"vehicleId\": 1, \"location\": \"Point(1 2)\"}"@2001-01-01');
-- 0141000138000000000000000200002008000080090000000A000000090000106C6F636174696F6E7...

Devolver la representación JSON de objetos en movimiento (MF-JSON)

asMFJSON(text) → tjsonb

SELECT asMFJSON(tjsonb '[{"Position": "Point(1 1)"}@2001-01-01,
  {"Position": "Point(2 2)"}@2001-01-02]');
/* {"type":"MovingJsonb","values":[{"Position": "Point(1 1)"},{"Position": "Point(2 2)"}],
   "datetimes":["2001-01-01T00:00:00+01","2001-01-02T00:00:00+01"],
   "lower_inc":true,"upper_inc":true,"interpolation":"Step"} */

Entrada a partir de la representación en texto bien conocido (WKT)

tjsonbFromText(text) → tjsonb

SELECT tjsonbFromText(text
  '[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
    {"vehicleId": 1, "location": "Point(2 2)"}@2001-01-02]');
/* [{"location": "Point(1 1)", "vehicleId": 1}@2001-01-01,
   {"location": "Point(2 2)", "vehicleId": 1}@2001-01-02] */

Entrada a partir de la representación en binario bien conocido (WKB) o en binario bien conocido hexadecimal (HexWKB)

tjsonbFromBinary(bytea) → tjsonb

tjsonbFromHexWKB(text) → tjsonb

SELECT tjsonbFromBinary(asBinary(
  tjsonb '[{"vehicleId": 1, "location": "Point(1 2)"}@2001-01-01]'));
-- [{"location": "Point(1 2)", "vehicleId": 1}@2001-01-01]
SELECT tjsonbFromHexWKB(asHexWKB(
  tjsonb '[{"vehicleId": 1, "location": "Point(1 2)"}@2001-01-01]'));
-- [{"location": "Point(1 2)", "vehicleId": 1}@2001-01-01]

Entrada a partir de la representación JSON de objetos en movimiento (MF-JSON)

tjsonbFromMFJSON(text) → tjsonb

SELECT tjsonbFromMFJSON('{"type": "MovingJsonb", "interpolation": "Step",
  "values": [{"Position": "Point(1 1)"}, {"Position": "Point(2 2)"}],
  "datetimes": ["2001-01-01T10:00:00Z","2001-01-01T12:00:00Z"]}');
/* [{"Position": "Point(1 1)"}@2001-01-01 11:00:00+01,
   {"Position": "Point(2 2)"}@2001-01-01 13:00:00+01] */