How to do JSON data validation with stored procedures in Postgresql

Question

I have one SQL table in postgress database having multiple JSON type columns. We want to do some kind of validation on those JSON coulmns before inserting the data.

Our target is to have validation using stored procedure, which call noSQL queries and respond error in case of any validation failure on that JSON column.

Example -

lets say we have 5 to 6 JSON columns in User table, JSON columns like -

Personal Information, Accounts, OwnerDetails etc, and these coulmns having complex JSON like list of JSON data or JSON within JSON.

Now we want to validate each column one by one and in case of error, return unique error code.

I am not going to do your work. I showcased below how to proceed with the validation of columns of JSON datatype. — i-blis
– i-blis, Commented Nov 26, 2015 at 13:50

i-blis · Accepted Answer · 2015-11-26 13:30:39Z

When using the JSON type, we can easily define constraints on the JSON fields. Here is a basic demonstration:

CREATE FUNCTION is_valid_description(descr text) RETURNS BOOLEAN AS
    'SELECT length($1) > 10;'
LANGUAGE sql;

CREATE TABLE wizards (
    id SERIAL, 
    data JSON,
    CONSTRAINT validate_name CHECK ((data->>'name') IS NOT NULL AND length(data->>'name') > 0),
    CONSTRAINT validate_description CHECK ((data->>'description') IS NOT NULL AND (is_valid_description(data->>'description')))
);

We check for constraints on two JSON fields of the data column. In one case, we use a stored procedure written in SQL, but we could write more complex functions with PL/pgSQL, or in Perl or Python.

We may also want to enforce uniqueness on one of the fields:

CREATE UNIQUE INDEX ui_wizards_name ON wizards((data->>'name'));

Validation and uniqueness constraints are then enforced when inserting or updating rows.

-- violates check constraint "validate_description" 
INSERT INTO wizards(data) VALUES('{
    "name": "Kirikou", 
    "description": "A witch"
}');

-- passes
INSERT INTO wizards(data) VALUES('{
    "name": "Kirikou", 
    "description": "A witch of African descent"
}');

-- violates unique constraint "ui_wizards_name"
INSERT INTO wizards(data) VALUES('{
    "name": "Kirikou", 
    "description": "The same witch of African descent"
}');

Code to play with on http://sqlfiddle.com/#!15/23974/2.

Collectives™ on Stack Overflow

How to do JSON data validation with stored procedures in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related