Postgres array of JSON avoid casting

Question

Issuing on PostgresSQL 9.2 the following:

CREATE TABLE test (j JSON, ja JSON[]);
INSERT INTO test (j) VALUES('{"name":"Alex", "age":20}' ); -- Works FINE
INSERT INTO test (ja) VALUES( ARRAY['{"name":"Alex", "age":20}', '{"name":"Peter", "age":24}'] ); -- Returns ERROR

The first insert works fine. The second insert returns error: column "ja" is of type json[] but expression is of type text[]

I can cast type to prevent the error:

INSERT INTO test(ja) VALUES( CAST (ARRAY['{"name":"Alex", "age":20}', '{"name":"Peter", "age":24}'] as JSON[]) ); -- Works FINE

My question is if there is a way to avoid casting?

Clodoaldo Neto · Accepted Answer · 2016-02-22 11:15:08Z

15

insert into test(ja) values
('{"{\"name\":\"alex\", \"age\":20}", "{\"name\":\"peter\", \"age\":24}"}');

To avoid the confuse escaping cast each string:

insert into test(ja) values
(array['{"name":"alex", "age":20}'::json, '{"name":"peter", "age":24}'::json]);

Or just cast the array itself:

insert into test (ja) values
(array['{"name":"alex", "age":20}', '{"name":"peter", "age":24}']::json[]);

edited Feb 22, 2016 at 11:15

answered Aug 7, 2013 at 11:32

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

rlib Over a year ago

Thanks for the reply, especially the second form.

Code Guru Over a year ago

can you please answer this stackoverflow.com/questions/51571453/…

Collectives™ on Stack Overflow

Postgres array of JSON avoid casting

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related