How can I update a value for a key in a jsonb object using a reference to the value itself?
I'm trying to update a key's value in a JSONB data type with a reference to itself. Basically I have to replace any negative values with it's positive.
I've tried this, and I can't seem to get it to work:
UPDATE transactions
SET raw_transaction = JSONB_SET( raw_transaction, '{transaction-amount}',
ABS ( ( raw_transaction ->> 'transaction-amount' )::DECIMAL ),
FALSE )
WHERE ( raw_transaction ->> 'transaction-amount' )::DECIMAL < 0
Sample data
{
"id":"wkjhen87398",
"transaction-amount":-1.23
}
Any help would be much appreciated.
Solved it with a few extra casts:
UPDATE transactions
SET raw_transaction = JSONB_SET( raw_transaction, '{transaction-amount}',
((ABS ( ( raw_transaction ->> 'transaction-amount' )::DECIMAL))::TEXT)::JSONB,
FALSE )
WHERE ( raw_transaction ->> 'transaction-amount' )::DECIMAL < 0