CRUD functions for storing JSON in postgresql

Let's create a table with an id and a json column. We use jsonb because postgresql offers us more functions to manipulate and use it.

create table jsonthing (
  id serial primary key,
  json jsonb not null
);

Here are the functions

create function jsonthing_select()
returns setof jsonthing
language plpgsql
as $$
begin
  return query select * from jsonthing;
end
$$;

create function jsonthing_insert(
  p_json jsonb)
returns setof jsonthing
language plpgsql
as $$
begin
  insert into jsonthing (json)
    values (p_json);
  return query select * from jsonthing;
end
$$;

create function jsonthing_update(
  p_id int,
  p_json jsonb)
returns setof jsonthing
language plpgsql
as $$
begin
  update jsonthing 
    set json = json || p_json
    where jsonthing.id = p_id;
  return query select * from jsonthing;
end
$$;

create function jsonthing_delete(
  p_id)
returns setof jsonthing
language plpgsql
as $$
begin
  delete from jsonthing
    where jsonthing.id = p_id;
  return query select * from jsonthing;
end
$$;

Note our jsonthing_update function merges the new json object with the existing one. This means it won't delete fields. Another function could replace the json object with little effort.

We use it like this below.

select * from jsonthing_insert(
  '{"akey":"avalue"}'
);

select * from jsonthing_insert(
  '{ "anotherkey":"anothervalue"}'
);

select * from jsonthing_update(1,
  '{ "additionalkey":"addtionalvalue"}'
);

select * from jsonthing_delete(1);

select * from jsonthing_select();
postgresql

Edit on github
comments powered by Disqus
Click me