Working with JSON in Postgres 9.5

Docker is an incredibly useful tool that allows for easy experimentation with new software. It enables the simultaneous installation, suspension, and deletion of multiple versions of programs, such as Postgres, with minimal effort. The installation process is as simple as executing the following command:

sudo apt-get install docker.io
sudo docker run --name postgres -e POSTGRES_PASSWORD=postgres -d postgres:9.5

Once Docker is installed, you can access an interactive Postgres shell by executing the following command:

sudo docker exec -it postgres psql -U postgres

With this tool, we can now delve into the new JSON functionality available in Postgres 9.5. We will begin by creating two tables, devices and observations, and adding some data to them:

CREATE TABLE sensors (
    id     integer PRIMARY KEY,
    type   character varying(64) NOT NULL,
    config jsonb NOT NULL
);

CREATE INDEX config_gin ON sensor USING gin(config);

INSERT INTO sensors (id, type, config)
     VALUES (1, 'soil-moisture',    '{ "alpha": 0.543, "beta": -2.312, "enabled": true }'),
            (2, 'soil-temperature', '{ "enabled": true, "depth": 0.24 }'),
            (3, 'humidity', '{ "enabled": false, "height": 1.34, "device": { "version": "3.4", "supported": true } }');

We can then enable the humidity sensor:

UPDATE sensors
   SET config = config || '{ "enabled": true }'::jsonb
 WHERE id = 3;

Remove the alpha and the beta parameters used for soil moisture calibration:

UPDATE sensors
   SET config = config - 'alpha' - 'beta'
 WHERE type = 'soil-moisture';

And now let’s remove the supported tag from all device sections:

UPDATE sensors
   SET config = config #- '{device,supported}';

Fetch the device version information wherever it’s available:

WITH versioning AS (
    SELECT id, type, config #>> '{device,version}' AS version
      FROM sensors
)
SELECT *
  FROM versioning
 WHERE version IS NOT NULL;

Find all the sensors where the depth is specified:

SELECT *
  FROM sensors
 WHERE config ? 'depth';

Let’s find all properly placed sensors, where either the depth or the height are specified, but not both:

SELECT *
  FROM sensors
 WHERE config ?| array['depth', 'height']
   AND NOT config ?& array['depth', 'height'];

For further reading on the JSON functionality available in Postgres 9.5, I recommend consulting the official documentation.