how to insert an array of json into postgresql column?

Question

create table test (jsonarraycolumn jsonb[]);

insert into test values (array['{"a": 45.50, "b": 10}', '{"a": 70.50, "b": 80}']);

I have created a table 'test'.

Trying to insert an array of json:

1st json:

'{"a": 45.50, "b": 10}'

2nd json:

'{"a": 70.50, "b": 80}'

I am getting the error:

SQL Error [42804]: ERROR: column "jsonarraycolumn" is of type jsonb[] but expression is of type text[]
  Hint: You will need to rewrite or cast the expression.
  Position: 26

How is an array of json inserted into a postgresql column?

Do you really want jsonb[]? It's a better practice to declare the column as jsonb, and then store an array json value in it: '[{"a": 45.50, "b": 10}, {"a": 70.50, "b": 80}'] — Bergi
– Bergi, Commented Jun 3, 2024 at 7:23
Does this answer your question? What is the use of JSONB[] (JSONB ARRAY) data type in PostgreSQL? — Mike Organek
– Mike Organek, Commented Jun 3, 2024 at 11:57

Dogbert · Accepted Answer · 2024-06-03 06:34:16Z

1

The type of array['...', '...'] is text[]. You need to cast it to jsonb[], just like the error message says:

insert into test values (array['{"a": 45.50, "b": 10}', '{"a": 70.50, "b": 80}']::jsonb[]);

answered Jun 3, 2024 at 6:34

Dogbert

224k43 gold badges419 silver badges416 bronze badges

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

Collectives™ on Stack Overflow

how to insert an array of json into postgresql column?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related