What's the difference between PostgreSQL array type vs array in json?

Questions / Help
1

I’m learning Ecto. The recommended way to work with embeded list seems to be using PostgreSQL array. Since json also supports array, I’m wondering what’s the difference?

2

Theres a good blog post you might find helpful: https://robots.thoughtbot.com/why-ecto-s-way-of-storing-embedded-lists-of-maps-makes-querying-hard

In general though, Postgresql has a native array type and a JSON(B) type. The Postgres array type can only be an array of simple types. JSON can, of course, be that and a lot more. With GIN indexing of the JSON document I think the performance of the two is sufficiently close to not be a major distinction. But as always its the data structure, desired normalisation and expected access paths the determine which one is more appropriate.

1 Like
3

Thanks for your reply! Will read the linked article.

But as always its the data structure, desired normalisation and expected access paths the determine which one is more appropriate.

Totally agree.