Type Mismatch when upserting with unnest

[israellot]

Describe the problem

Please describe the issue you observed, and any steps we can take to reproduce it:

To Reproduce

create table test(id string primary key, a jsonb)

upsert into test(id,a) values ('1','{"a":"b"}')

upsert into test(id,a) 
	select * from unnest(array['1','2'],array['{"a":"b"}','{"c":"d"}']) as d

The first upsert works well, string is converted to jsonb
The second upsert fails with type mismatch error

SQL Error [42804]: ERROR: value type string doesn't match type jsonb of column "a"

Environment:

• CockroachDB version 20.1.8

This is just an example, my interest is on bulk loading data using unnest and passing array parameters , such as
upsert into test(id,a) select * from unnest(@p1,@p2)

[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 was unable to automatically find someone to ping.

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]

I think this is because we don't have support for arrays of JSON elements. #23468

[ajwerner]

I think it may also be related to #38965 though could be wrong. It feels like even if we did support those arrays, I'm not sure we'd automatically figure out that we need to interpret that array as a JSONB array and not a string array.

[rafiss]

I'll go ahead and close this in favor of the two linked issues.

thanks for the report @israellot ! Feel free to comment on the other issues if you have more details to share.