38

I can't find any information about JSON schema validation in PostgreSQL, is there any way to implement JSON Schema validation on PostgreSQL JSON data type?

khorvat
  • 1,630
  • 2
  • 20
  • 31

3 Answers3

33

There is another PostgreSQL extension that implements json validation. The usage is almost the same as "Postgres-JSON-schema"

CREATE TABLE example (id serial PRIMARY KEY, data jsonb);
-- do is_jsonb_valid instead of validate_json_schema
ALTER TABLE example ADD CONSTRAINT data_is_valid CHECK (is_jsonb_valid('{"type": "object"}', data));

INSERT INTO example (data) VALUES ('{}');
-- INSERT 0 1

INSERT INTO example (data) VALUES ('1');
-- ERROR:  new row for relation "example" violates check constraint "data_is_valid"
-- DETAIL:  Failing row contains (2, 1).

I've done some benchmarking validating tweets and it is 20x faster than "Postgres-JSON-schema", mostly because it is written in C instead of SQL.

Disclaimer, I've written this extension.

Gabriel Furstenheim
  • 2,969
  • 30
  • 27
23

There is a PostgreSQL extension that implements JSON Schema validation in PL/PgSQL.

It is used like this (taken from the project README file):

CREATE TABLE example (id serial PRIMARY KEY, data jsonb);
ALTER TABLE example ADD CONSTRAINT data_is_valid CHECK (validate_json_schema('{"type": "object"}', data));

INSERT INTO example (data) VALUES ('{}');
-- INSERT 0 1

INSERT INTO example (data) VALUES ('1');
-- ERROR:  new row for relation "example" violates check constraint "data_is_valid"
-- DETAIL:  Failing row contains (2, 1).
Mordae
  • 342
  • 3
  • 4
  • 1
    Did you know if there are any performance - load tests for this validation ? – khorvat Dec 14 '16 at 13:12
  • I do not know. The code is quite short (259 lines) of a pretty straight-forward PL/PgSQL, which is interpreted. You should measure it with your own schema. – Mordae Dec 15 '16 at 12:10
2

What you need is something to translate JSON Schema constraints into PostgreSQL ones, e.g.:

{
    "properties": {
        "age": {"minimum": 21}
    },
    "required": ["age"]
}

to:

SELECT FROM ...
WHERE (elem->>'age' >= 21)

I'm not aware of any existing tools. I know of something similar for MySQL which might be useful for writing your own, but nothing for using the JSON type in PostgreSQL.

cloudfeet
  • 12,156
  • 1
  • 56
  • 57
  • I thought that there isn't schema validation available at this point, do you know if there is a chance that we use V8 engine for validation e.g. in some OnUpdate/OnInsert actions ? – khorvat Mar 06 '14 at 20:19
  • Oh! Sorry, I misunderstood and thought you were asking about using JSON Schema for queries, not validation. Yeah, validating as a hook, or even validating in code before you submit seems reasonable. – cloudfeet Mar 07 '14 at 13:17
  • 4
    check out this resource. might be helpful. http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-data-validation.html – topwik Nov 24 '14 at 18:28