How to set timestamp key-value pair in nested jsonb postgres column

I am trying to use the postgres sql script below to set the timestamp key-value pair of column 'ack' but I get the following error. Does anyone know how to correct it? I don't run into issues when the value is just a simple string instead of a timestamp.

--Sql script
UPDATE TABLE_NAME set ack=jsonb_set(ack , '{clientZ}', '{"timestamp": "ABCDE"}')

--Result I am getting 
{"clientZ": {"timestamp": "ABCDE"}} -- this works fine

--Sql script 
UPDATE TABLE_NAME set ack=jsonb_set(ack , '{clientZ}', '{"timestamp": to_jsonb(to_char(now(), ''YYYY-MM-DD HH:MI:SS.MS TZHTZM''))}')

--Result I am expecting 
{"clientZ": {"timestamp": "2024-10-30 11:31:38.765 -0500"}}

--Error I am getting 
SQL Error [42601]: ERROR: syntax error at or near "to_jsonb"

Thanks for the question! I think you got quite close to a solution.

The third argument to jsonb_set must be a valid JSON. In your case, it is '{"timestamp": to_jsonb(to_char(now(), ''YYYY-MM-DD HH:MI:SS.MS TZHTZM''))}', which is not valid JSON (the to_jsonb(... part lacks quotes). The to_jsonb function is not evaluated within a string, but taken letter for letter. This can be seen from just converting the string to JSON:

-- gives an error about bad JSON
select '{"timestamp": to_jsonb(to_char(now(), ''YYYY-MM-DD HH:MI:SS.MS TZHTZM''))}'::jsonb;

If you want to replace the whole JSON object, you have to construct it from its elements:

select jsonb_build_object('timestamp', to_char(now(), 'YYYY-MM-DD HH:MI:SS.MS TZHTZM'));

The jsonb_build_object takes a key and a value (or many of them, alternativly).

Then you can use the result as the replacement value, like this:

select jsonb_set('{"clientZ":{"timestamp":"X"}}', 
                 '{clientZ}',
                 jsonb_build_object('timestamp', to_char(now(), 'YYYY-MM-DD HH:MI:SS.MS TZHTZM')));

In your case, it does not seem to be necessary to replace the whole object containing the timestamp. If you can get away with just setting the timestamp field alone, move the timestamp into the path array:

select jsonb_set('{"clientZ":{"timestamp":"X"}}',
                 '{clientZ,timestamp}',
                 to_jsonb(to_char(now(), 'YYYY-MM-DD HH:MI:SS.MS TZHTZM')::text));

And we get

There are of course other ways, like building the string representation of the JSON object by concatenating all the parts, but it will be tedious to properly quote all parts, so this seems like an easier solution.