Postgresql – Calling a function inside of JSONB

functionsjsonpostgresql

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;

Best Answer

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..