CODEWITHSUNDEEP
jsonpostgresql
David Masip
David Masip

Reputation: 2491

json-ize string column in postgresql

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?

Upvotes: 1

Views: 89

Answers (1)

user330315
user330315

Reputation:

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+'));

Upvotes: 3

Related Questions