CODEWITHSUNDEEP
sqljsonpostgresqljsonb
Matt
Matt

Reputation: 22921

postgres: ensure json column root is an object

I'm wondering how to ensure that the data inserted into a json or jsonb column is an object, not an array (or an array of objects).

Example:

-- ok
insert into users (settings) values ('{ "theme": "cobalt" }')

-- ok
insert into users (settings) values ('{}')

-- error!
insert into users (settings) values ('[]')

-- error!
insert into users (settings) values ('[{}]')

Thanks!

Upvotes: 0

Views: 98

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51579

you could do smth like:

t=# create table so16(j jsonb check (left(ltrim(j::text), 1) <> '['));
CREATE TABLE
t=# insert into so16 values('{"b":[1,2,3]}');
INSERT 0 1
t=# insert into so16 values('[1,2,3]');
ERROR:  new row for relation "so16" violates check constraint "so16_j_check"
DETAIL:  Failing row contains ([1, 2, 3]).
t=# insert into so16 values('   [1,2,3]');
ERROR:  new row for relation "so16" violates check constraint "so16_j_check"
DETAIL:  Failing row contains ([1, 2, 3]).

Upvotes: 1

Related Questions