Jsonb columns

JSON fields in the SensorThings API

SensorThings API defines two fields that can contain JSON: Thing/properties and Observation/parameters. You can also filter on that json, using queries like: v1.0/Datastreams(1)/Observations?$filter=parameters/depth lt 10

By default, the SensorThingsService stores this json in a text field. This means that for every query like this, it has to parse the json. This is of course not very good for performance.

If you use many queries like this, you can convert these two text fields to the jsonb type. This also allows you to add indices on the json, so you can further improve your queries.

Converting JSON columns to jsonb

First, we need to add an automatic cast from large text objects to jsonb. PostgreSQL does not automatically cast from text to json, but we do not want to change the source-code to explicitly insert jsonb, since that would break the cases where the columns are still text. Therefore we need to have PostgreSQL handle the conversion.

CREATE OR REPLACE FUNCTION text_to_jsonb(text) RETURNS jsonb AS $$
SELECT $1::jsonb; 
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION oid_to_jsonb(oid) RETURNS jsonb AS $$
SELECT encode(lo_get($1),'escape')::jsonb; 
$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (oid AS jsonb) WITH FUNCTION oid_to_jsonb(oid) AS IMPLICIT;
CREATE CAST (varchar AS jsonb) WITH FUNCTION text_to_jsonb(text) AS IMPLICIT;

Next, we can convert the existing text columns to jsonb. Make a backup of your database first!

alter table "DATASTREAMS" alter column "PROPERTIES" set data type jsonb USING "PROPERTIES"::jsonb;
alter table "FEATURES" alter column "PROPERTIES" set data type jsonb USING "PROPERTIES"::jsonb;
alter table "LOCATIONS" alter column "PROPERTIES" set data type jsonb USING "PROPERTIES"::jsonb;
alter table "MULTI_DATASTREAMS" alter column "PROPERTIES" set data type jsonb USING "PROPERTIES"::jsonb;
alter table "MULTI_DATASTREAMS" alter column "OBSERVATION_TYPES" set data type jsonb USING "OBSERVATION_TYPES"::jsonb;
alter table "MULTI_DATASTREAMS" alter column "UNIT_OF_MEASUREMENTS" set data type jsonb USING "UNIT_OF_MEASUREMENTS"::jsonb;
alter table "OBSERVATIONS" alter column "PARAMETERS" set data type jsonb USING "PARAMETERS"::jsonb;
alter table "OBSERVATIONS" alter column "RESULT_JSON" set data type jsonb USING "RESULT_JSON"::jsonb;
alter table "OBS_PROPERTIES" alter column "PROPERTIES" set data type jsonb USING "PROPERTIES"::jsonb;
alter table "SENSORS" alter column "PROPERTIES" set data type jsonb USING "PROPERTIES"::jsonb;
alter table "THINGS" alter column "PROPERTIES" set data type jsonb USING "PROPERTIES"::jsonb;

That's it, your queries should now be faster already. If you often use specific queries, you can also add indices for those queries.