PostgreSQL - transform varchar set to json-CodePudding

One of the columns from my table contains varchar data in the form: {"item1", "item2", "item3"}. The number of elements may vary, the column is also nullable.

I would like to change the type of this column to json. After the change, the data should be in the format:

[{"name": "SOME_NAME", "item": "item1"}, {"name": "SOME_NAME", "item": "item2"}, {"name": "SOME_NAME", "item": "item3"}]

Value SOME_NAME is fixed and will be the same for all elements.

So having:

 ----------------- 
| items (varchar) |
 ----------------- 
| {item1,item2}   |
| null            |
| {item3,item4}   |
 -----------------

I'd like to have a new column with:


 ---------------------------------------------------------------------------------- 
|                               new_items (json)                                   |
 ---------------------------------------------------------------------------------- 
| [{"name": "SOME_NAME", "item": "item1"}, {"name": "SOME_NAME", "item": "item2"}] |
| null                                                                             |
| [{"name": "SOME_NAME", "item": "item3"}, {"name": "SOME_NAME", "item": "item4"}] |
 ----------------------------------------------------------------------------------

I tried with json_build_object() and similar json functions, but I didn't get the intended effect.

CodePudding user response：

I believe you can get your data back as array of text with

with first_sel as (
    select '{"item1", "item2", "item3"}' col_text
    )
select col_text::text[] from first_sel;

result

      col_text
---------------------
 {item1,item2,item3}

Get them in different rows with the unnest

with first_sel as (
    select '{"item1", "item2", "item3"}' col_text
    )
select unnest(col_text::text[]) from first_sel;

Result

 unnest
--------
 item1
 item2
 item3

Then you can build the single JSON entries with json_build_object

with first_sel as (
    select '{"item1", "item2", "item3"}' col_text
    ),
list_of_rows as(
    select unnest(col_text::text[]) items from first_sel
    )
select 
    json_build_object('SOME_NAME',items) from list_of_rows

Result

    json_build_object
-------------------------
 {"SOME_NAME" : "item1"}
 {"SOME_NAME" : "item2"}
 {"SOME_NAME" : "item3"}
(3 rows)

and then aggregate them with json_agg

with first_sel as (
    select '{"item1", "item2", "item3"}' col_text
    ),
list_of_rows as(
    select unnest(col_text::text[]) items from first_sel
    ),
single_entries as (
    select json_build_object('SOME_NAME',items) json_obj from list_of_rows)
select 
    json_agg(json_obj)
    from single_entries;

Result

                                  json_agg
-----------------------------------------------------------------------------
 [{"SOME_NAME" : "item1"}, {"SOME_NAME" : "item2"}, {"SOME_NAME" : "item3"}]

Edit: since the subject of the original question now spans more rows. If you have a column id for which you can group by, then use that, otherwise you can:

Create a similar table with the following

create table test (col_text text);
insert into test values ('{"item1", "item2", "item3"}');
insert into test values (NULL);
insert into test values ('{"item4", "item5", "item6"}');

Use the ctid as group by information

with list_of_rows as(
    select ctid id, unnest(col_text::text[]) items from test
    ),
single_entries as (
    select id, json_build_object('SOME_NAME',items) json_obj from list_of_rows)
select 
    id,
    json_agg(json_obj)
    from single_entries
    group by id;

result

  id   |                                  json_agg
------- -----------------------------------------------------------------------------
 (0,1) | [{"SOME_NAME" : "item1"}, {"SOME_NAME" : "item2"}, {"SOME_NAME" : "item3"}]
 (0,3) | [{"SOME_NAME" : "item4"}, {"SOME_NAME" : "item5"}, {"SOME_NAME" : "item6"}]
(2 rows)