sql/pgwire: need support for decoding JSON[] OID by aliasing to JSONB[] OID [unknown oid type: 199]

[danieljharvey]

Describe the problem

Since the fix in #88355 , CockroachDB recognises oid 114 as JSON, and internally aliases it to JSONB (with the oid 3802).

Would it be possible to make the same fix with JSON arrays? Ideally it would treat oid 199 (JSON[]) as oid 3807 (JSONB[])?

If we send the same queries with 3807 instead everything works.

Expected behavior
Ideally, JSON[] input with oid 199 would be accepted in the wire format and treated as JSONB[] internally.

Additional context
Discovered whilst implementing subscriptions for Hasura Graphql Engine

Expected oid values found in here: https://github.com/postgres/postgres/blob/5543677ec90a15c73dab5ed4f0902b3b920f0b87/src/include/catalog/pg_type.dat#L443

Jira issue: CRDB-20967
Epic CRDB-11916

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @rafiss (assigned to "unknown oid type: 114" when sending JSON data in wire format #88355)
• @cockroachdb/sql-experience (found keywords: Hasura)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

We could do this, but CRDB does not support JSON arrays well right now (#23468). I expect that if we fix this, it would just fail at the next step.

We do have limited support for JSON arrays in aggregate functions (#70041), but I don't expect it to work as a query argument. Based on what you're saying, you got things to work with oid 3807. Could you share example code or queries of what you tried?

[rafiss]

Here is an example that we can use to test the changes we make:

CREATE TABLE example (id INTEGER PRIMARY KEY, name TEXT);


SELECT "_subs"."result_id",
       "_fld_resp"."root" AS "result"
FROM unnest($1::UUID[], $2::JSON[]) AS "_subs"("result_id",
                                                   "result_vars")
LEFT OUTER JOIN LATERAL
  (SELECT json_build_object('hasura_example', "hasura_example"."root") AS "root"
   FROM
     (SELECT coalesce(json_agg("root"), '[]') AS "root"
      FROM
        (SELECT row_to_json(
                              (SELECT "_e"
                               FROM
                                 (SELECT "_root.base"."id" AS "id", "_root.base"."name" AS "name") AS "_e")) AS "root"
         FROM
           (SELECT *
            FROM "example"
            WHERE ('true') ) AS "_root.base") AS "_root") AS "hasura_example") AS "_fld_resp" ON ('true');

• For $1 we're sending an array of UUIDs as unknown (sample value '{35638bc7-5d39-4860-8002-de1c4bd937c4}')
• For $2 we're sending an array of JSON as oid 3807 (sample value: ARRAY['{"cursor":{},"query":{},"session":{},"synthetic":[]}'])