How to convert a comma separated string to an array in postgresql

Question

I have a json string '{"1001": "20", "1002": "30", "1003": "50"}' Which I have turned into a column with this select statement

SELECT json_each_text (vdn_weight) as weightage 
FROM aa_dev.sdg_metadata;

Now I want to convert this into a 2d array such that the comma-separated values goes like

arr[1][1] = 1001

arr[1][2] = 1002

arr[1][3] = 1004

arr[2][1] = 20

arr[2][2] = 30

arr[2][3] = 50

I have tried to write a query to update the above select statement and remove the round braces

With CTE
AS
(SELECT json_each_text (vdn_weight) as weightage
FROM data )
UPDATE CTE
set CTE.weightage = regexp_replace(CTE.weightage, '[\(\) "]', '', 'g');

but this query gave me an error

SQL Error [42P01]: ERROR: relation "cte" does not exist
  Position: 105

What can be a better solution or a better data structure apart from 2d array? I can not use a temp table or table.

S-Man · Accepted Answer · 2021-03-03 14:21:01Z

You cannot UPDATE a CTE, only real tables.

step-by-step demo:db<>fiddle

SELECT
    array_agg(                                                    -- 3
        array[key, value]                                         -- 2
    )
FROM json_each_text('{"1001": "20", "1002": "30", "1003": "50"}') -- 1

When you use json_each_text() in the FROM list, it generates two columns (key and value) instead of a tuple
Put the values from these created columns together into an array
Aggregate all records into a two-dimensional array.

If you want to first put all keys into one array and all values into one array and aggregate these arrays afterwards (switch dimensions), then have to switch the operations, of course:

demo:db<>fiddle

SELECT
    array[
        array_agg(key),
        array_agg(value)
    ]
FROM json_each_text('{"1001": "20", "1002": "30", "1003": "50"}')

Can I convert the array of the array into an array variable like arr[][] instead of the column if a select query?
I am not quite sure what you mean. dbfiddle.uk/… It is now possible to access the array as you describe: a[1][1] returns 1001.

Collectives™ on Stack Overflow

How to convert a comma separated string to an array in postgresql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related