postgresql add tag
Truilus
Occasionally I see table definitions that use an array of jsonb (i.e. `jsonb[]`) rather than a JSON value with an array. 

So why would one use

```
create table data
(
  ... other columns ...
  payload jsonb[] not null
);

insert into data (payload)
values 
(array['{"key1": "value1"}', '{"key2": "value2"}']);
```

over 

```
create table data
(
  ... other columns ...
  paylod jsonb not null
);

insert into data (payload)
values 
('[{"key1": "value1"}', '{"key2": "value2"}]');
```

As I see the first solution every now and then I start wondering if there is some upside to that solution compared to the "json only" solution that I don't see. 

With the second solution everything is "json", when accessing data, I don't have to mix native array operators/functions with json operators/functions. Which I think is a lot easier to deal with. 

Can anyone see a real benefit of mixing native arrays with jsonb rather than simply using JSON arrays? (Or any downside of **only** using `jsonb`)

Obviously this assumes that de-normalizing the model indeed makes sense to begin with.






Top Answer
Jack Douglas
I'm really glad I read all the way to the end of the question:

> Obviously this assumes that de-normalizing the model indeed makes sense to begin with

So if we can assume JSON is the correct choice, we are only answering the hypothetical question:

> Can anyone see a real benefit of mixing native arrays with jsonb rather than simply using JSON arrays? (Or any downside of only using jsonb)

There are two differences that may be sigificant enough in some particular use case to justify using a native array of JSON elements over a JSON array:

1. You want to constrain the type of the field to be an array

   If you simply make the field type `jsonb`, then it can contain an array, but it could also contain any other type, such as an object.

2. You are going to want to unnest the array, and `unnest` is faster than `jsonb_array_elements`:

    <>https://dbfiddle.uk/?rdbms=postgres_13&fiddle=2a2a90addd4ffa3081fee09b0c2dbf2d&hide=1

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.