Converting postgres string column to json

Question

I have a user table where I want to convert a string column foo to a json with {"oldfield": < the value of foo in the current row >}, but I can't seem to find the right syntax. I'm directly using the psql command line interface.

This is what I tried:

 ALTER TABLE user_schema.user ALTER COLUMN "foo" SET DATA TYPE json USING '{"oldfield": "foo"}';

This changes the field to exactly {"oldfield":"foo"}, and not that actual value of the foo column. I've also tried the below combos, all of which fail with a syntax error. Any ideas? Thanks

   ALTER TABLE user_schema.user ALTER COLUMN "foo" SET DATA TYPE json USING '{"oldfield": foo}';
ERROR:  invalid input syntax for type json
DETAIL:  Token "foo" is invalid.
CONTEXT:  JSON data, line 1: {"oldfield": foo...

 ALTER TABLE user_schema.user ALTER COLUMN "foo" SET DATA TYPE json USING '{"oldfield": \"foo\"}';
ERROR:  invalid input syntax for type json
DETAIL:  Token "\" is invalid.
CONTEXT:  JSON data, line 1: {"oldfield": \...
ALTER TABLE user_schema.user ALTER COLUMN "foo" SET DATA TYPE json USING '{"oldfield": 'foo'}';
ERROR:  syntax error at or near "foo"
LINE 1: ...Metadata" SET DATA TYPE json USING '{"oldfield": 'foo...
                                                             ^
ALTER TABLE user_schema.user ALTER COLUMN "foo" SET DATA TYPE json USING '{"oldfield": `foo`}';
ERROR:  invalid input syntax for type json
DETAIL:  Token "`" is invalid.
CONTEXT:  JSON data, line 1: {"oldfield": `...
 ALTER TABLE user_schema.user ALTER COLUMN "foo" SET DATA TYPE json USING '{"oldfield": user.foo}';
ERROR:  invalid input syntax for type json
DETAIL:  Token "user" is invalid.
CONTEXT:  JSON data, line 1: {"oldfield": user...
ALTER TABLE user_schema.user ALTER COLUMN "foo" SET DATA TYPE json USING '{"oldfield": user_schema.user.foo}';
ERROR:  invalid input syntax for type json
DETAIL:  Token "user_schema" is invalid.
CONTEXT:  JSON data, line 1: {"oldfield": user_schema...
ALTER TABLE user_schema.user ALTER COLUMN "foo" SET DATA TYPE json USING '{"oldfield": foo}';
ERROR:  invalid input syntax for type json
DETAIL:  Token "foo" is invalid.

klin · Accepted Answer · 2018-02-16 01:22:45Z

4

Use the function json_build_object():

create table my_table(foo text);
insert into my_table values
    ('first'),
    ('second');

alter table my_table 
    alter column foo set data type json 
    using json_build_object('oldfield', foo);

select *
from my_table;

           foo           
-------------------------
 {"oldfield" : "first"}
 {"oldfield" : "second"}
(2 rows)

answered Feb 16, 2018 at 1:22

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

user3125693 Over a year ago

Thank you, this did it. This was my syntax if the table is inside a schema (unfortunately it did take me 5 minutes to figure out, but maybe i'm just not used to sql syntax and when to quote/refer to table...): ALTER TABLE user_schema.user ALTER COLUMN "Foo" SET DATA TYPE json USING json_build_object('oldfield', "Foo");

Collectives™ on Stack Overflow

Converting postgres string column to json

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related