Postgrest: Writing a JSON array to a native array column fails

[motiz88]

I came across this while developing jsgrest:

Given a table like CREATE TABLE t (ints int[]), if I POST the payload {"ints": [1,2,3]} to /t, I get error 22P02 malformed array literal: "[1,2,3]". Targeting a column of type json or jsonb with the same payload works, as does writing out the SQL array literal explicitly to match the column type: {"ints": "{1,2,3}"}.

It seems that array values must be formatted with reference to some schema knowledge, and that currently they're assumed to always be JSON arrays. I don't know if there's a way around this - I'm kind of hoping there's a way to signal to pg "here's an array value that can be coerced to int[] or json/jsonb as needed", but it's not clear to me that the Postgres protocol supports this. Anyhow you might want to document this quirk, have explicit tests for it etc.

[begriffs]

Thanks for pointing out the array literal issue. We've run into similar stuff such as #282 and #328. For the time being we've been storing arrays in json columns, but real postgres array support is too fundamental to overlook forever.

[matthewmueller]

This seems pretty fundamental. For example, saving oauth scopes, the natural schema design would be something like this: scopes []text. I think a lot of folks will be bitten by this early on.

Not too familiar with the codebase, but it seems like on Postgres 9.4+, there's just needs to be a check if the schema type is an array and this function: json_array_elements_text(json)

ref: http://dba.stackexchange.com/a/54289

[allochi]

Any updates or plans to address this? I would have loved to help, but I only recently started learning Haskell.

Currently the only workaround for me is to preprocess in Javascript arrays as strings formatted in Postgres way with {} instead of [], but it seems that Postgrest should be able to do that for me.

Appreciate any advice or help, I could be missing something. Thanks!

[begriffs]

The workaround, if your db design allows it, is to use json or jsonb for the column type rather than text array. I had kind of lost track of this issue but I'll mark it as something to look into. It's not high priority for me at the moment, but I'll look into the good suggestion about json_array_elements_text.

[allochi]

thanks @begriffs, but then I can't use ?tags=cs.{example, new} or ?values=cd.{1,2,3} in queries, since I can't post tags field as an array, I'm not sure how to achieve the same results with jsonb array, but I will look it up.

I would still recommend implementing it.

[allochi]

@begriffs thanks, I stand corrected, jsonb arrays works, and the queries using cs and cd works too. Thanks a lot.

[Tehvan]

Bump

[steve-chavez]

I've tested the POST /t {"ints": [1,2,3]} in Postgresql 10 and it succeeds, no need for an array literal.

In the PG 10 changelog:

With this change, array-type fields in the destination SQL type are properly converted from JSON arrays.

[steve-chavez]

Closing. Also as a side note I think we should avoid adding more json processing logic since it'll contribute to more memory usage specially in bulk inserts, we already have a problem with this, see https://github.com/begriffs/postgrest/issues/690.