HTTP proxy doesn't handle array of json documents as quer parameter

[hlinnaka]

The /sql interface of the http proxy doesn't handle an array of json documents as query parameter.

A plain json/jsonb query parameter works, and an array of any other datatypes also works, but an array of json that does not.

Steps to reproduce

set CONNSTR env variable to any neon database url and run:

$ curl -s -d '{"query": "select ($1::jsonb[])", "params": [[{"foo": 1}]]}' -H "Neon-Connection-String: $CONNSTR" -H "Content-Type: application/sql" https://ep-tight-math-08339409.eu-central-1.aws.neon.tech/sql |jq
{
  "code": "22P02",
  "message": "db error: ERROR: malformed array literal: \"{{\"foo\":1}}\"\nDETAIL: Unexpected array element.\n\nCaused by:\n    ERROR: malformed array literal: \"{{\"foo\":1}}\"\n    DETAIL: Unexpected array element."
}

Expected result

No error. The query parameter is a single-element array with a json document, so the result should be the same as:

neondb=> select array['{"foo": 1}'];
      array       
------------------
 {"{\"foo\": 1}"}
(1 row)

Logs, links

Reported by a user over slack.

[khanova]

The reason of this bug is that in PostgreSQL there is no direct casting from string to jsonb array (jsonb[]).

The provided example is incorrect in sense that there is no option to do select ($1::jsonb[]) for all possible arguments.

I am not sure if this problem is our responsibility to fix.

[hlinnaka]

The reason of this bug is that in PostgreSQL there is no direct casting from string to jsonb array (jsonb[]).

The provided example is incorrect in sense that there is no option to do select ($1::jsonb[]) for all possible arguments.

I am not sure if this problem is our responsibility to fix.

There is a cast from string to all datatypes in PostgreSQL, including jsonb[]. SELECT $1::jsonb[] is perfectly valid. For example:

neondb=> prepare mypstmt as select $1::jsonb[];
PREPARE
neondb=> execute mypstmt ('{"{\"foo\": 1}"}');
      jsonb       
------------------
 {"{\"foo\": 1}"}
(1 row)

The string has to be a correctly formatted string representation of jsonb[]. The problem arises if you pass the query parameter as plain JSON to the HTTP proxy. The conversion in the HTTP proxy from an array of json documents to text produces a string that PostgreSQL doesn't understand.

I think the fix needs to go somewhere in https://github.com/neondatabase/neon/blob/5158de70f392ea1ff7224414d8a90c60ce7a5d16/proxy/src/http/sql_over_http.rs#L95. Not sure where exactly...