Postgresql update column with integer values

Question

I've a column with jsonb type and contains list of elements either in string or integer format. What I want now is to make all of them as same type e.g either all int or all string format

Tried: this way I get single element but I need to update all of the elements inside of the list.

SELECT parent_path -> 1 AS path
    FROM abc
    LIMIT 10

OR

Update abc SET parent_path = ARRAY[parent_path]::TEXT[] AS parent_path
FROM abc

OR

UPDATE abc SET parent_path = replace(parent_path::text, '"', '') where id=123

Current Output

         path
[6123697, 178, 6023099]
[625953521394212864, 117, 6023181]
["153", "6288361", "553248635949090971"]
[553248635358954983, 178320, 174, 6022967]
[6050684, 6050648, 120, 6022967]
[653, 178238, 6239135, 38, 6023117]
["153", "6288496", "553248635977039112"]
[553248635998143523, 6023185]
[553248635976194501, 6022967]
[553248635976195634, 6022967]

Expected Output

        path
[6123697, 178, 6023099]
[625953521394212864, 117, 6023181]
[153, 6288361, 553248635949090971]   <----
[553248635358954983, 178320, 174, 6022967]
[6050684, 6050648, 120, 6022967]
[653, 178238, 6239135, 38, 6023117]
[153, 6288496, 553248635977039112]   <----
[553248635998143523, 6023185]
[553248635976194501, 6022967]
[553248635976195634, 6022967]

Note: Missing double quotes on the list. I've tried several methods from here but no luck

score 5 · Accepted Answer · 2020-11-02 12:37:27Z

5

You will have to unnest them, cleanup each element, then aggregate it back to an array:

The following converts all elements to integers:

select (select jsonb_agg(x.i::bigint order by idx)
        from jsonb_array_elements_text(a.path) with ordinality as x(i, idx)
       ) as clean_path
from abc a;

edited Nov 2, 2020 at 12:37

answered Nov 2, 2020 at 12:28

user330315

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

Comments

Ruben Helsloot · Accepted Answer · 2020-11-02 12:31:55Z

1

You can use a scalar subquery to select, unnest, and aggregate the elements:

WITH mytable AS (
    SELECT row_number() over () as id, col::JSONB
    FROM (VALUES ('[6123697, 178, 6023099]'),
                 ('["6123697", "178", "6023099"]')) as bla(col)
)
SELECT id, (SELECT JSONB_AGG(el::int) FROM jsonb_array_elements_text(col) as el)
FROM mytable

answered Nov 2, 2020 at 12:31

Ruben Helsloot

13.2k6 gold badges33 silver badges56 bronze badges

Collectives™ on Stack Overflow

Postgresql update column with integer values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related