Split single JSON array column and update multiple integer columns with this data

I have a table with a JSON array column containing an integer of every hour of the day, e.g.:

[0, 10, 15, 20, 25, 30, 44, 62, 47, 20, 6, 10, 26, 42, 39, 21, 6, 0, 0, 0, 0, 0, 0, 0]

In the same table I have added 24 new integer columns named i0 till i23 so I can easier filter on each individual column without complicated JSON queries.

With which SQL query can I update the 24 columns with the values from the JSON array, like the example below? I need to split the JSON array into 24 values and meanwhile update each row in the same table.

i0=0, i1=10, i2=15, ..... i23=0

I tried something like this, but then I’m getting a error ‘Query 1 ERROR: ERROR: cannot cast type json to integer’

UPDATE table
set 
i0=CAST (jsoncol::json->0 AS INTEGER),
i1=CAST (jsoncol::json->1 AS INTEGER)

Answer

I fixed it using the query below. Is there a better method?

    UPDATE table
    set 
    i0=(jsoncol::json->0)::TEXT::INT,
    i1=(jsoncol::json->1)::TEXT::INT,
    i2=(jsoncol::json->2)::TEXT::INT,
    i3=(jsoncol::json->3)::TEXT::INT,
    i4=(jsoncol::json->4)::TEXT::INT,
    ....
    etc