problem inserting json array into postgres: malformed error-CodePudding

I'm trying to insert an array of JSON values into a postgres database. But I'm getting this error:

error: malformed array literal: "{{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]},{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]}}"

If I'm correct, the array literal should take the form "{{...},{...}}". As far as I know, this is the correct way to insert an array of JSON elements into a postgres table.

Here's my whole query if it helps:

INSERT INTO main(prev_snippets, next_snippets, req_characters, description, requirements, prereq_events, character_effects, world_effects, character_decisions)
        VALUES ('{"22","45","99"}', '{"33","22"}', '{{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]},{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]}}', 'cvdfgfg', NULL, NULL, NULL, NULL, NULL);

prev_snippets and next_snippets are arrays of integers (I'll make those integers instead of strings when I get the JSON problem figured out), req_characters is a JSONB array, and description is text.

Please let me know how I can make my question better. Thanks a ton for the help.

Update:

Here's the table definition:

CREATE TABLE main (
  id SERIAL NOT NULL UNIQUE,
  PRIMARY KEY(id),
  prev_snippets INTEGER[],
  next_snippets INTEGER[],
  req_characters JSONB[],
  description TEXT NOT NULL,
  requirements JSONB[],
  prereq_events JSONB[],
  character_effects JSONB[],
  world_effects JSONB[],
  character_decisions JSONB[]
);

I believe I'm using postgres version 14.

CodePudding user response：

You've mixed up JSONB arrays with Postgres arrays.

req_characters JSONB[] is a Postgres array of JSONB values. This would be something like...

array['{"traits":...}','{"traits":...}'].

Embedding JSON in a Postgres array is unnecessarily complex. JSON already has arrays. In JSON, {} is a key/value object. [] is an array of elements.

Instead of using jsonb[] simply use jsonb and pass a JSON array.

'[{"traits":...}, {"traits":...}]'.

And for simplicity, I would suggest changing all the other arrays to simply be JSONB.

See JSON.org for more.

CodePudding user response：

Probably the easiest way to do this with a Postgres array is :

insert into array_test 
 (jsonb_array) 
values 
(ARRAY['{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]}'::jsonb, 
'{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]}'::jsonb]);

select jsonb_array[1] from array_test ;
                                    jsonb_array                                     
------------------------------------------------------------------------------------
 {"traits": {"belief": [], "physical": [], "personality": []}, "relationships": []}

Though if it where me I would just us a jsonb field not a Postgres array of jsonb elements.