Validating json string using CHECK constraint in Postgres (sql)

Question

I have a table with below schema :

CREATE TABLE tbl_name (
id bigserial primary key,
phone_info json
);

Sample json data for phone_info column is given below .

 {  
   "STATUS":{"1010101010":"1","2020202020":"1"},
   "1010101010":"OK", 
   "2020202020":"OK"  
 }

Now I need to add a check constraint on phone_info column so that all key for "STATUS" ie(1010101010,2020202020) should exist as a (key,value) pair of phone_info column where value would be "OK". So above sample data would satisfy the check constraint as there are following key value pair exists in phone_info column.

"1010101010":"OK"
"2020202020,":"OK"

I have tried below solution but this has not worked because array_agg function is not supported with check constraints.

ALTER TABLE tbl_name
ADD CONSTRAINT validate_info CHECK ('OK' = ALL(array_agg(phone_info->json_object_keys(phone_info->'STATUS'))) );

Can someone please help me out , Can I write a SQL function and use the function in check constraint?

Craig Ringer · Accepted Answer · 2014-01-27 09:20:11Z

3

With something like this I think you'll want an SQL function.

CREATE TABLE tjson AS SELECT '{  
   "STATUS":{"1010101010":"1","2020202020":"1"},
   "1010101010":"OK", 
   "2020202020":"OK"  
 }'::json AS col;

perhaps something like:

CREATE OR REPLACE FUNCTION my_json_valid(json) RETURNS boolean AS $$
SELECT bool_and(coalesce($1->>k = 'OK','f'))
FROM json_object_keys($1->'STATUS') k;
$$ LANGUAGE sql IMMUTABLE;

... but remember that while PostgreSQL will let you modify that function, doing so can cause previously valid rows to become invalid in the table. Never modify this function without dropping the constraint then adding it back again.

answered Jan 27, 2014 at 9:20

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anant Over a year ago

Thank you very much :) . I added the function and it is working fine with check constraint .

Collectives™ on Stack Overflow

Validating json string using CHECK constraint in Postgres (sql)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related