Question 207737 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to set a new object in jsonb using value from another field

I have a table named acount with json column data. There is a field called amount and I need to create a new object in that json and copy the value of amount to one of the fields inside. I tried something like this and none of those worked.

update account a set data = data::jsonb || ('{"accountedAmount": {"amount": null, "currency": null, "amountCzk": ' || a.data->'amount' || '}}' )::jsonb where (a.data->'amount') is not null;

update account a set data = data::jsonb || ('{"accountedAmount": {"amount": null, "currency": null, "amountCzk": ' || jsonb_extract_path(data, 'amount') || '}}' )::jsonb where (a.data->'amount') is not null;

There is an error:

[22P02] ERROR: invalid input syntax for type json Detail: The input string ended unexpectedly.

How do I copy that value in a simple way?

Tags:

2

Answers


  1. 0

    Use the ->> operator to extract text for concatenation, not ->, i.e. data ->> 'amount' or respectively the json_extract_path_text function.

    update account set data = data::jsonb || 
 ('{"accountedAmount": {"amount": null, "currency": null, "amountCzk": ' ||
  (data->>'amount') ||
 '}}')::jsonb
where data ? 'amount';
    Login or Signup to reply.
  2. 0

    The problem here is the order of operations. You are appending a.data to the partial string, and then it is trying to interpret that partial result as json so it can be accessed by ->, all before the closing ‘}}’ are seen. You need to clarify that the -> applies only to a.data, by putting parenthesis around it. But then you still have the problem that the partial fragment is still trying to convert to json, because you are in doing unknown || json. This gets resolved by assuming the literal of unknown type should be json, but of course it is not valid as json being only a fragment. You could either explicitly label it as text, or use ->> so the 2nd argument is known to be text.

    '{"accountedAmount": {"amount": null, "currency": null, "amountCzk": '::text || (a.data->>'amount') || '}}'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search