CODEWITHSUNDEEP
postgresqljsonb
Jonathan
Jonathan

Reputation: 16349

Postgres: How to casting JSONB value to numeric

I am having issues casting a jsonb value. And would love some guidance.

what we are trying to achieve is that some data came in as strings, and we want to cast that to numbers.

Consider the following update statement:

update customer
set traits = jsonb_set(traits, '{arr}',traits->'arr'::text::integer)
where jsonb_typeof(traits->'arr') = 'string'
  and traits->'arr' is not null

We currently get the following error:

[22P02] ERROR: invalid input syntax for type integer: "arr"

I have tried all sort of casting incantations, but can't figure away past this.

Anyone have a path forward for us ?!

working solution looks like THIS:

update customer
set traits = jsonb_set(traits, '{arr}',(traits->>'arr')::integer::text::jsonb)
where jsonb_typeof(traits->'arr') = 'string'
  and traits->'arr' is not null

with a triple cast. Smells a bit off

Upvotes: 1

Views: 2611

Answers (2)

Aabesh Karmacharya
Aabesh Karmacharya

Reputation: 768

Since Postgres 14, you can do:

UPDATE customer
SET traits['arr'] = traits['arr']::integer::text::jsonb
WHERE jsonb_typeof(traits['arr']) = 'string'
  AND traits['arr'] IS NOT null;

Upvotes: 1

Bergi
Bergi

Reputation: 664297

The problem is that your expression

traits->'arr'::text::integer

is evaluated as

traits->('arr'::text::integer)

which is trying to cast 'arr' to an integer (failing for obvious reasons with the error message you mention). Instead, you want

(traits->'arr')::text::integer
-- or
(traits->>'arr')::integer

Upvotes: 5

Related Questions