json-ize string column in postgresql

Question

I have the following data structure:

create table test (tags VARCHAR, tags_json VARCHAR);

insert into test (tags, tags_json)
values ('A B', '["A", "B"]')

And I want to convert the column tags to a JSON column. If I were to do it with the tags_json column is pretty easy:

select tags_json::JSON from test

But when I run it using the tags column,

select tags::JSON from test

I get

SQL Error [22P02]: ERROR: invalid input syntax for type json

How can I convert the column tags to a JSON column in postgresql?

What do you want to happen with the invalid JSON? What do you expect 'A B' to be in a JSON representation? — user330315
– user330315, Commented Aug 13, 2020 at 7:14

user330315 · Accepted Answer · 2020-08-13 07:16:14Z

3

You need to first convert your "plain text" to an array, then you can use to_jsonb() to convert that to a proper JSON value:

select to_jsonb(regexp_split_to_array(tags, '\s+'))
from test;

If you want to permanently change the column's data type, you can use that expression in an ALTER statement:

alter table test
  alter tags type jsonb 
     using to_jsonb(regexp_split_to_array(tags, '\s+'));

answered Aug 13, 2020 at 7:16

user330315

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

1 Answer 1