How to insert JSON containing escape codes into a JSONB column in PostgreSQL using GORM

I'm trying to store the JSON bytes to PostgreSQL, but there's a problem.

> \u0000 cannot be converted to text.

As you can see below, the JSON contains escape sequences such as \u0000, which it seems PostgreSQL is interpreting as unicode characters, not JSON strings.

err := raws.SaveRawData(data, url)
// if there is "\u0000" in the bytes
if err.Error() == "ERROR: unsupported Unicode escape sequence (SQLSTATE 22P05)" {
	// try to remove \u0000, but not work
    data = bytes.Trim(data, "\u0000")
	e := raws.SaveRawData(data, url) // save data again
	if e != nil {
		return e // return the same error
	}
	return nil
}

Origin API data can be access form Here. There is \u0000 in it:

{
  "code": 0,
  "message": "0",
  "ttl": 1,
  "data": {
    "bvid": "BV1jb411C7m3",
    "aid": 42443484,
    "videos": 1,
    "tid": 172,
    "tname": "手机游戏",
    "copyright": 1,
    "pic": "http://i0.hdslb.com/bfs/archive/c76ee4798bf2ba0efc8449bcb3577d508321c6c5.jpg",
    "title": "冰塔：我连你的大招都敢硬抗，所以告诉我谁才是生物女王？！单s冰塔怒砍档案女王巴德尔，谁，才是生物一姐？（手动滑稽）",
    "pubdate": 1549100438,
    "ctime": 1549100438,
    "desc": "bgm：逮虾户\n今天先水一期冰塔的，明天再水\\u0000绿塔的，后天就可以下红莲啦，计划通嘿嘿嘿(º﹃º )",
    "desc_v2": [
      {
        "raw_text": "bgm：逮虾户\n今天先水一期冰塔的，明天再水\\u0000绿塔的，后天就可以下红莲啦，计划通嘿嘿嘿(º﹃º )",
        "type": 1,
        "biz_id": 0
      }
    ],
    "state": 0,
    "duration": 265,
    "rights": {
      "bp": 0,
      "elec": 0,
      "download": 1,
      "movie": 0,
      "pay": 0,
      "hd5": 0,
      "no_reprint": 1,
      "autoplay": 1,
      "ugc_pay": 0,
      "is_cooperation": 0,
      "ugc_pay_preview": 0,
      "no_background": 0,
      "clean_mode": 0,
      "is_stein_gate": 0
    },
    "owner": {
      "mid": 39699039,
      "name": "明眸-雅望",
      "face": "http://i0.hdslb.com/bfs/face/240f74f8706955119575ea6c6cb1d31892f93800.jpg"
    },
    "stat": {
      "aid": 42443484,
      "view": 1107,
      "danmaku": 7,
      "reply": 22,
      "favorite": 5,
      "coin": 4,
      "share": 0,
      "now_rank": 0,
      "his_rank": 0,
      "like": 10,
      "dislike": 0,
      "evaluation": "",
      "argue_msg": ""
    },
    "dynamic": "#崩坏3#",
    "cid": 74479750,
    "dimension": {
      "width": 1280,
      "height": 720,
      "rotate": 0
    },
    "no_cache": false,
    "pages": [
      {
        "cid": 74479750,
        "page": 1,
        "from": "vupload",
        "part": "冰塔：我连你的大招都敢硬抗，所以告诉我谁才是生物女王？！单s冰塔怒砍档案女王巴德尔，谁，才是生物一姐？（手动滑稽）",
        "duration": 265,
        "vid": "",
        "weblink": "",
        "dimension": {
          "width": 1280,
          "height": 720,
          "rotate": 0
        }
      }
    ],
    "subtitle": {
      "allow_submit": false,
      "list": []
    },
    "user_garb": {
      "url_image_ani_cut": ""
    }
  }
}

The struct for save is:

type RawJSONData struct {
	ID        uint64         `gorm:"primarykey" json:"id"`
	CreatedAt time.Time      `json:"-"`
	DeletedAt gorm.DeletedAt `json:"-" gorm:"index"`
	Data      datatypes.JSON `json:"data"`
	URL       string         `gorm:"index" json:"url"`
}

datatypes.JSON is from gorm.io/datatypes. It seems just is json.RawMessage, it is (extend from?) a []byte.

I use PostgreSQL's JSONB type for storage this data.

Table:

create table raw_json_data
(
    id         bigserial not null constraint raw_json_data_pke primary key,
    created_at timestamp with time zone,
    deleted_at timestamp with time zone,
    data       jsonb,
    url        text
);

The Unicode escape sequence \u0000 is simply not supported in Postgres TEXT and JSONB columns:

> The jsonb type also rejects \u0000 (because that cannot be represented in PostgreSQL's text type)

You can change the column type to JSON:

create table Foo (test JSON);
insert into Foo (test) values ('{"text": "明天再水\u0000绿塔的"}');
-- works

> The json data type stores an exact copy of the input text

This has the advantage of keeping the data identical to what you received from the API, in case the escape sequence has some meaning that you need to preserve.

It'll also allow you to query using Postgres JSON operators (e.g. ->>), albeit converting a JSON field with \u0000 to text will still fail:

select test->>'text' from Foo
-- ERROR:  unsupported Unicode escape sequence

<hr>

Columns of type BYTEA also accept any byte sequence without having to manipulate the data. In Gorm, use type:bytea tag:

type RawJSONData struct {
// ... other fields
Data      string `gorm:"type:bytea" json:"data"`
}

<hr>

If any of the above is not acceptable for you, then you must sanitize the input string...