JSON/B fails on insert when Simple Protocol is used.

[james-lawrence]

var c1 pgtype.JSON
config.PreferSimpleProtocol = true
db := stdlib.OpenDB(config)
c1.Set("[]")
db.Query("INSERT INTO foo (json_field) VALUES ($1)", c1)

results in the following error:

  Expected success, but got an error:
      <*pgconn.PgError | 0xc0004ec0f0>: {
          Severity: "ERROR",
          Code: "22P02",
          Message: "invalid input syntax for type json",
          Detail: "Token \"\\\" is invalid.",
          Hint: "",
          Position: 788,
          InternalPosition: 0,
          InternalQuery: "",
          Where: "JSON data, line 1: \\...",
          SchemaName: "",
          TableName: "",
          ColumnName: "",
          DataTypeName: "",
          ConstraintName: "",
          File: "jsonfuncs.c",
          Line: 617,
          Routine: "json_ereport_error",
      }
      ERROR: invalid input syntax for type json (SQLSTATE 22P02)

it seems like it has to do with the call to func (src JSON) Value() (driver.Value, error) { and changing it to return a string instead of bytes resolves the issue.

that being said I'm not sure if that is a good solution or I am just missing an option.

I'll try to write a test case this weekend.

[jackc]

Unless you have a very unusual use case you shouldn't need to use pgtype.JSON directly at all. Just use a string or []byte directly.

[james-lawrence]

even so wouldn't expect the failure when using pgtype.JSON.

[jackc]

Hmm... That might be interesting. The whole string vs []byte thing with JSON has caused problems before. It might work to just change it in Value() but I'm not sure of the implications.

[james-lawrence]

same which is why i didn't just open a PR as the fix. there is code still hanging around that doesn't get executed anymore because the typing has changed. so far I havent seen any problems with the change in our system but we use the simple protocol exclusively. might be worth just changing and seeing if all the tests pass as they're more robust against the normal code path than the simple protocol path. (and obv add a new test for the simple pipeline)

[Yiivgeny]

Set() with struct causes error too

[guillaq]

Just FYI, I ended up on this Github issue after experiencing the same issue without using pgtype.JSON. I am using gorm and I had something like

raw, _ := json.Marshal(l)

err = r.db.WithContext(ctx).Model(&models.User{}).Where("id = ?", id).
    Update("locations", gorm.Expr("jsonb_insert(locations, '{0}', ?)", raw)).Error

It worked fine without PreferSimpleProtocol but failed when I set it to true.

using string(raw) solved the issue