PostgreSQL Auto-increment inside a JSON

Question

Is it possible to auto-increment inside PostgreSQL's new JSON type using just SQL (like serial) and not server code?

Answer 1

I can't really imagine why you'd want to, but sure.

CREATE SEQUENCE whywouldyou_jsoncol_seq;

CREATE TABLE whywouldyou (
    jsoncol json not null default json_object(ARRAY['id'], ARRAY[nextval('whywouldyou_jsoncol_seq')::text]),
    dummydata text;
);

ALTER SEQUENCE whywouldyou_jsoncol_seq OWNED BY whywouldyou.jsoncol;

insert into whywouldyou(dummydata) values('');

select * from whywouldyou;

   jsoncol    | dummydata 
--------------+-----------
 {"id" : "1"} | 
(1 row)

Note that with this particular formulation it's the string "1" not the number 1 in the json. You might want to form the json object another way if you want to avoid that. This is just an example.