Part of JSON from String to Integer

Question

I have a text field with JSON structure in my Postgres v10.8 DB. I need to grab a value inside the config and cast it to a whole number. (values are in format like this 0.0, 1.0, 2.0) It's never 1.5 so it should be possible to turn it into whole numbers.

With this select i can grab the value i need but i don't know how i can turn the results from this into a whole number?

select coalesce(cast(tb.config_exit as json)->> 'exit_time_tenant' , '') as exit_time from table tb

When i try to say cast('1.0' as Integer) i get this ERROR: invalid input syntax for integer: "1.0"

This works but it's probably not a good solution?

cast(substring('1.0' from '([0-9]+)(.{1})') as Integer)

{"cd":"09","mv_er_cd":"060","exit_time_tenant":"4.0","ex":"4.0","earliest_date":"2026-09-30","earliest_date_mv":[],"mb_in":null,"mb_out":null} — user2210516
– user2210516, Commented Jan 18, 2022 at 9:09

Pooya · Accepted Answer · 2022-01-18 10:51:37Z

0

You should convert tb.config_exit :: json ->> 'exit_time_tenant' between parentheses and then convert to decimal

Demo

select 
  (tb.config_exit :: json ->> 'exit_time_tenant') :: decimal :: int as exit_time 
from 
  "table" tb

edited Jan 18, 2022 at 10:51

answered Jan 18, 2022 at 9:45

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

2 Comments

user2210516 Over a year ago

I have the same problem with decimal as with text. Im not allowed to send 4.0 as decimal. I has to be 4 as int. The program that handles my output is expecting an Int.

Pooya Over a year ago

If you need to convert to int add ::int after convert decimal (I updated post)

Collectives™ on Stack Overflow

Part of JSON from String to Integer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related