problem inserting json array into postgres: malformed error

Question

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.

To your question add the table definition, in particular the actual declared types of the columns. — Adrian Klaver
– Adrian Klaver, Commented Oct 13, 2021 at 18:38
Are you 100% sure about all the array datatypes? It's usually a bad database design — Frank Heikens
– Frank Heikens, Commented Oct 13, 2021 at 18:49
@FrankHeikens Good point. Based on the suggestions here, I'm changing all my JSONB array columns into just JSONB columns, since it's redundant. I might also switch to Mongo DB, as this is starting to look less like a relational database use case. — StLouis9
– StLouis9, Commented Oct 13, 2021 at 19:05

Schwern · Accepted Answer · 2021-10-13 19:01:43Z

5

You've mixed up JSONB 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":...}]'.

Most languages have built-in facilities to convert data to JSON. This is much simpler than embedding JSON in a Postgres Array. See JSON.org for more.

For the same reason, I would suggest changing all the other arrays to simply be JSONB.

Also note that arrays and JSON are not a replacement for join tables. All of your arrays might be better done as join tables.

edited Oct 13, 2021 at 19:01

answered Oct 13, 2021 at 18:45

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

2 Comments

StLouis9 Over a year ago

Maybe I'm misunderstanding your answer -- I'm trying to make an array of JSON elements. so the outer {} is to signify an array, and the second level of {} is to signify a JSON element.

Schwern Over a year ago

@StLouis9 I just caught that you've declared it jsonb[] not jsonb. See my updated answer.

Adrian Klaver · Accepted Answer · 2021-10-13 19:06:57Z

4

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 use a jsonb field not a Postgres array of jsonb elements.

edited Oct 13, 2021 at 19:06

answered Oct 13, 2021 at 18:48

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

problem inserting json array into postgres: malformed error

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related