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 https://github.com/gavinwahl/postgres-json-schema.git
    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
    /usr/pgsql-10/bin/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";
    CREATE EXTENSION
   

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]).
    postgres=#
   

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

Thank you

–Raghav