Postgres: How to casting JSONB value to numeric

Question

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

Bergi · Accepted Answer · 2020-10-29 14:57:46Z

5

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

answered Oct 29, 2020 at 14:57

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jonathan Over a year ago

I think thats the ticket -- but still have a follow up issue with my jsonb_set.

[42883] ERROR: function jsonb_set(jsonb, unknown, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 30

is my path syntax incorrect??

Bergi Over a year ago

@Jonathan I think because jsonb_set is overloaded, postgres isn't clever enough to infer the type of the '{arr}' literal. You need to write '{arr}'::text[] (ARRAY['arr'] might also work).

Jonathan Over a year ago

this is what I had to do -- does this make sense to you / feel right??:

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

Bergi Over a year ago

It doesn't f"eel right" to me as I would hope there would be a simpler/shorter/better way, but it's probably the correct way to do it.

Bergi Over a year ago

Instead of int_value::text::jsonb one can do to_jsonb(int_value), that makes it look better.

Aabesh Karmacharya · Accepted Answer · 2023-05-05 05:28:27Z

1

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;

answered May 5, 2023 at 5:28

Aabesh Karmacharya

7689 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgres: How to casting JSONB value to numeric

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related