Support primitive collections as JSON (instead of an array)

[roji]

The default - and natural - way to map a primitive collection (e.g. int[]) in PostgreSQL is to a database array (the PG integer[]) type. This is PG-unique feature; in other database, primitive collections are mapped to a JSON text column in the database.

However, in some cases it may still make sense to map primitive collections to JSON in PG, just like we do in other databases. While we seem to support the mapping itself, most forms of querying won't work (e.g. we need to translate to jsonb_to_recordset instead of unnest).

Since mapping to PG arrays is the recommended way, this isn't likely to be high priority. Putting this in the backlog to gather user feedback.

Note: one reason to do this would be to allow nested collections (once EF itself supports these in relational). PostgreSQL doesn't support arrays of arrays, so it's impossible to support nested collections when mapping to arrays; but JSON would work. Think about whether this should be automatic, or require some sort of opt in to make it clear that it's JSON rather than arrays.

[roderickgreen]

Another place this would be handy is mapping multidimensional primitive array columns (int[,]) since those aren't supported by native array mapping.

[roji]

@roderickgreen given that PostgreSQL fully supports multidimensional arrays, isn't that a better option? Note that the provider does partially support multidimensional arrays (#314 tracks completing this).

[roderickgreen]

@roji yeah my preference would be to use native pg multidimensional arrays. That was my first approach but I got an error in my migration about it not being supported, so I switched to the json integration and ended up here. I'll circle back to native arrays and press a bit harder. Thanks for following up.

Edit: I know what happened using multidimensional arrays. I was trying to move some data to the new array column using migrationBuilder.UpdateData within the migration and that doesn't yet support multidimension array literals. I can work with that. And I agree this obviates the need for json support for this usecase.