"wrong element type" when using JSONBArray using pgx

Question

I am trying to insert a new row that has an inventory with data type jsonb[]:

elements := []pgtype.Text{{String: `{"adsda": "asdasd"}`, Status: pgtype.Present}}
dimensions := []pgtype.ArrayDimension{{Length: 1, LowerBound: 1}}
inventory := pgtype.JSONBArray{Elements: elements, Dimensions: dimensions, Status: pgtype.Present}
row = db.pool.QueryRow(context.Background(), `INSERT INTO user ("email", "password", "inventory") VALUES($1, $2, $3) RETURNING uuid, email, "password"`, requestEmail, requestPassword, inventory)

But I get the following error:

"Severity": "ERROR",
"Code": "42804",
"Message": "wrong element type",
"Detail": "",
"Hint": "",
"Position": 0,
"InternalPosition": 0,
"InternalQuery": "",
"Where": "",
"SchemaName": "",
"TableName": "",
"ColumnName": "",
"DataTypeName": "",
"ConstraintName": "",
"File": "arrayfuncs.c",
"Line": 1316,
"Routine": "array_recv"

Postgres table definition:

CREATE TABLE public.user (
    uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
    email varchar(64) NOT NULL,
    "password" varchar(32) NOT NULL,
    inventory _jsonb NULL,
    CONSTRAINT user_pk PRIMARY KEY (uuid)
);

What might be the issue? Any idea would help.

Answer 1

Type jsonb[] in pgx was broken

As for the error message you report:

"Severity": "ERROR",
"Code": "42804",
"Message": "wrong element type",
...

The Guthub page on pgx reveals:

The binary format can be substantially faster, which is what the pgx interface uses.

So you are using the binary protocol. For this, the data types have to use a compatible binary format, and it seems that ARRAY of jsonb is not encoded properly? Related:

• PostgreSQL/PostGIS - PQexecParams - wrong element type

Luckily for you, the author seems to have fixed this just yesterday: (!)

jackc: Fix JSONBArray to have elements of JSONB

Your problem should go away once you install the latest version containing commit 79b05217d14ece98b13c69ba3358b47248ab4bbc

jsonb[] vs. jsonb with nested JSON array

It might be simpler to use a plain jsonb instead of jsonb[]. JSON can nest arrays by itself. Consider:

SELECT '[{"id": 1}
       , {"txt": "something"}]'::jsonb  AS jsonb_array
     , '{"{\"id\": 1}"
        ,"{\"txt\": \"something\"}"}'::jsonb[] AS pg_array_of_jsonb;

Either can be unnested in Postgres:

SELECT jsonb_array_elements('[{"id": 1}, {"txt": "something"}]'::jsonb) AS jsonb_element_from_json_array;

SELECT unnest('{"{\"id\": 1}","{\"txt\": \"something\"}"}'::jsonb[]) AS jsonb_element_from_pg_array;

Same result.

db<>fiddle here

That should also avoid your error.

Additional error

Your INSERT command:

INSERT INTO user ("email", "password", "inventory") VALUES ...

... should really raise this:

ERROR: syntax error at or near "user"

Because user is a reserved word. You would have to double-quote it to make it work. But rather don't use user it as Postgres identifier. Ever.

The table creation works because there the tablename is schema-qualified, which makes it unambiguous:

CREATE TABLE public.user ( ...