如何使用GORM将包含JSON的转义码插入PostgreSQL中的JSONB列

Question

我试图将JSON字节存储到PostgreSQL中，但出现了问题。

\u0000无法转换为文本。

如下所示，JSON包含u0000等转义序列，PostgreSQL似乎将其解释为unicode字符，而不是JSON字符串。

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
}

来源API数据可以从这里访问。其中有\u0000：

{
"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": ""
}
}
}

保存的结构是：

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"`
}

CCD_ 2来自CCD_。它似乎只是json.RawMessage，它是(从？扩展而来的([]byte。

我使用PostgreSQL的JSONB类型来存储这些数据。

表：

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
);

Answer 1

PostgresTEXT和JSONB列根本不支持Unicode转义序列u0000：

jsonb类型也拒绝\u0000(因为它不能在PostgreSQL的文本类型中表示(

您可以将列类型更改为JSON:

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

json数据类型存储输入文本的精确副本

这样做的优点是，在转义序列有一些需要保留的含义的情况下，保持数据与从API接收到的数据相同。

它还允许您使用Postgres JSON运算符(例如->>(进行查询，尽管将带有u0000的JSON字段转换为文本仍然会失败：

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

---

BYTEA类型的列也接受任何字节序列，而不必操作数据。在Gorm中，使用type:bytea标签：

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

---

如果以上任何一项都不可接受，则必须清除输入字符串。。。