Re: convert from json to text[]

On Wed, Jun 26, 2013 at 8:34 PM, Mason Leung <h2opbun(at)gmail(dot)com> wrote:
> Hi,
>
> I am running postgres 9.2.4 and 1 of my columns (column name is old_field)
> is type json. When I select from this table, I get the following sample
> data
>
> select old_field from table1;
>
> [],
> ['a', 'b']
> ['a']
> []
>
> How do I change the data type from json to text[]?
>
> I have tried
>
> alter table table1 add column new_field text[] default '{}'::text[]"
> UPDATE table1 SET new_field = {old_field} where old_field is not null"
>
> Questions:
> 1) how to I change the data type from json to text[]
> 2) how to I select rows that has data and not [],

json deserialization was heavily enhanced in the upcoming 9.3 release
which would make dealing with this triival. for now, you have to
fire up pl/v8 or another pl with heavy json support or implement a
crude parser in sql :/

merlin