Calling a function inside of JSONB?

Question

Is is possible to call a function, like now() when inserting/updating a JSONB column in Postgres?

I was thinking something like the following where time would be set to the return value of now(): SELECT '{"value": 5, "time": now()}'::JSONB;

Answer 1

Sure, JSON doesn't support a special date type, (neither does JavaScript), JSONB only supports

• string
• number
• boolean
• null

So just use string interpolation, it'll get stored the same way:

SELECT (
  '{"value": 5, "time": "' || now() || '" }'
)::JSONB;

If you're trying to simply get NOW() from the server in one call, you can certainly do that using jsonb_set, or jsonb concatenation (||). Here are the docs

SELECT '{"value": 5}'::JSONB || jsonb_build_object('time', now());

In a third party library this may look like..

INSERT INTO table (json)
VALUES ( ? || jsonb_build_object('time', now()) );

It's still ugly. I wouldn't store a datatime in a JSONB, unless I had to:

• It will be slow.
• It will take more space.

As an aside, one of the alternatives to JSONB that Pg could have chosen is BSON which doesn't store dates as strings..

Answer 2

Just because you mentioned node-postgres now() "as json value" does not need jsonb_build_object if you pass it as prepared statement, Eg:

var pg = require('pg');
var conString = 'postgres://n@10.1.10.199/b';
var pgClient = new pg.Client(conString);
pgClient.connect();

var query = 'select concat(\'{"value": 5, "time": "\',$2::timestamptz,\'"}\')::jsonb, $1::timestamptz';
var values = ['now()','now()'];
var cb = function(err, result) {
  console.log(result.rows);
};

pgClient.query(
  {
    name: 'get some',
    text: query,
    values: values
  }
  , cb
);

shows:

[
  {
    concat: {
      time: '2016-12-09 14:16:28.539636+00',
      value: 5
    },
    timestamptz: FriDec09201614: 16: 28GMT+0000(GMTStandardTime)
  }
]