CODEWITHSUNDEEP
sqljsondatabasepostgresqldatabase-design
m0meni
m0meni

Reputation: 16435

When to use JSON[] instead of JSON?

Say I'm inserting an array of JSON objects into Postgres, for example:

[{ s: 'hi', a: true, b: false }, { s: 'bye', a: false, b: true }]

I can either do:

create table test(a json);
insert into test values ('[{ "s": "hi", "a": true, "b": false }, { "s": "bye", "a": false, "b": true }]');

or

create table test2(a json[]);
insert into test2 values (array['{ "s": "hi", "a": true, "b": false }', '{ "s": "bye", "a": false, "b": true }']::json[]);

what are the advantages/disadvantages of either approach?

Upvotes: 0

Views: 70

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 1

Do not ever mix the two.

  1. There is a small space and dereferencing overhead with jsonb.
  2. There is a large space overhead and a small deferencing overhead with ARRAY
  3. There is a larger overhead in space, and even larger overhead in dereferencing with JSON[] (detriments compound).

Not to mention, you'll have two sets of operators and it will be confusing as hell.

Also, don't use JSON unless you know when to use JSON. You almost always want JSONB. To the point that I wouldn't even worry about the corner cases.

Upvotes: 4

Related Questions