up vote 1 down vote favorite

We have a large amount of PLPGSQL code that inserts/update json data into JSON columns using Postgres 9.3

We want to convert the code over time to move to the JSONB format and take advantage of the better indexing and new operators.

Is there a way of doing this without rewriting all of our code to use the new JSONB format.

For example, we have a lot of functions that accept and return JSON, crude example:-

FUNCTION foo(payload JSON) RETURNS JSON AS
$$
  BEGIN
  ...
  INSERT INTO baa(json, name) VALUES(payload, 'name');
  RETURN '{"message" : "done"}'::JSON

  END 
$$ LANGUAGE PLPGSQL;

My ideal would be something that CASTS the json to jsonb without having to rewrite the function. I tried a trigger function to do this prior to insert but this does not work as the postgres query syntax checker gets there first:-

ERROR:  column "json" is of type jsonb but expression is of type json

I suspect there is nothing I can do and will have to rewrite all of the code (1000s of lines), but thought I'd put this out there to see if there is a clever idea that could allow me to do the CAST on insert/update and buy some time for the rewrite.

share|improve this question
up vote 2 down vote

It's simple enough to introduce an implicit cast between json and jsonb, but you shouldn't do it. Adapting your code is the correct thing to do, despite being annoying.

The reason is that when you cast between json and jsonb you incur a significant performance hit. PostgreSQL has to parse the json into a hash table, or format the jsonb hash table back to flat json text. Lots of memory allocations, lots of processor work, lots of CPU cache churn. You don't want to be doing that all the time.

There are also semantic differences. In particular, jsonb doesn't preserve the order of keys in an object, and doesn't preserve duplicate keys. json does. So some_json_col :: jsonb :: json isn't the same as some_json_col.

A decent text editor and find/replace will make your job pretty easy, even over large amounts of code. Time to get editing.

share|improve this answer
    
Thanks, I understand your reasoning but the code changes are not as simple as find/replace. There are some pieces I want to leave as JSON. When you say "it's simple enough to introduce an implicit cast between json and jsonb", can you please expand as to how you would do this in relation to my question and I will then accept the answer. – user1331131 Aug 13 '15 at 8:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.