Validating JSON data type column in PostgreSQL

Published on Thu, Aug 23, 2018

In this post, we see how to validate the JSON/JSONB data type column in PostgreSQL 10. Most of the basic JSON/JSONB validation has been taken care in core of PostgreSQL itself, however you may have a requirement to validate the JSON format elements data types while inserting. For such requirements, you need to write a Pl/PgSQL function to validate the JSON data when accepting it in JSON/JSONB column in a CHECK constraint. “postgres-json-schema” extension already made it available with same methodology and I believe author also actively updating it. Let’s try the extension.

Note: Before proceeding with the steps, make sure you have PostgreSQL 10 installed with dependencies.

Configure postgres-json-schema extension

  1. Clone “postgres-json-schema” repository from here

     [root@master ~]# git clone
     Cloning into 'postgres-json-schema'...
     remote: Counting objects: 39, done.
     remote: Compressing objects: 100% (3/3), done.
     remote: Total 39 (delta 0), reused 1 (delta 0), pack-reused 36
     Unpacking objects: 100% (39/39), done.
  2. Set your PostgreSQL installation “pg_config” location in PATH and verify.

     [root@master ~]# export PATH=/usr/pgsql-10/bin:$PATH
     [root@master ~]# which pg_config
  3. Change to cloned “postgres-json-schema” directory and run “make install”

     [root@master ~]# cd postgres-json-schema/
     [root@master postgres-json-schema]# make install
     /usr/bin/mkdir -p '/usr/pgsql-10/share/extension'
     /usr/bin/mkdir -p '/usr/pgsql-10/share/extension'
     /usr/bin/install -c -m 644 .//postgres-json-schema.control '/usr/pgsql-10/share/extension/'
     /usr/bin/install -c -m 644 .//postgres-json-schema--0.1.0.sql  '/usr/pgsql-10/share/extension/'
  4. Connect to the database to create the “postgres-json-schema” extension. Extension creates “validate_json_schema” Pl/PgSQL function, which can be called against JSON data column in CHECK constraint.

     [root@master postgres-json-schema]# psql -U postgres -p 5432 -d postgres
     psql (10.5)
     Type "help" for help.
     postgres=# \dx
                      List of installed extensions
       Name   | Version |   Schema   |         Description
      plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
     (1 row)
     postgres=# CREATE EXTENSION "postgres-json-schema";
  5. Create a sample table with JSONB column by enabling extension function.

     CREATE TABLE json_table (
       id serial PRIMARY KEY,
       data jsonb CHECK (validate_json_schema($${"type": "array","items": {"anyOf": [{"type": "integer"},{"type": "string"}]}}$$,data)));
  6. Now try, inserting the JSON data

     postgres=# INSERT INTO json_table (data) VALUES ('[1, 2, "a"]');
     INSERT 0 1
     postgres=# INSERT INTO json_table (data) VALUES ('[1, 2, 1]');
     INSERT 0 1
     postgres=# INSERT INTO json_table (data) VALUES ('[1, 2, null]');
     ERROR:  new row for relation "json_table" violates check constraint "json_table_data_check"
     DETAIL:  Failing row contains (4, [1, 2, null]).

You can play with above extension which gives flexibility to validate the JSON/JSONB data types.

Thank you