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 … Read more
The reason behind this, is that in PostgreSQL (up to 9.3) there is no equality operator defined for json (i.e. val1::json = val2::json will always throw this exception) — in 9.4 there will be one for the jsonb type. One workaround is, you can cast your json field to text. But that won’t cover all … Read more
Update: for 9.5+, there are explicit operators you can use with jsonb (if you have a json typed column, you can use casts to apply a modification): Deleting a key (or an index) from a JSON object (or, from an array) can be done with the – operator: SELECT jsonb ‘{“a”:1,”b”:2}’ – ‘a’, — will … Read more
Update: With PostgreSQL 9.5, there are some jsonb manipulation functionality within PostgreSQL itself (but none for json; casts are required to manipulate json values). Merging 2 (or more) JSON objects (or concatenating arrays): SELECT jsonb ‘{“a”:1}’ || jsonb ‘{“b”:2}’, — will yield jsonb ‘{“a”:1,”b”:2}’ jsonb ‘[“a”,1]’ || jsonb ‘[“b”,2]’ — will yield jsonb ‘[“a”,1,”b”,2]’ So, … Read more
First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be texts (however values can be sql NULLs too). Both json & jsonb allows you to store a valid JSON value (defined in its spec). F.ex. these are valid JSON representations: null, true, … Read more