使用SQLAlchemy向PostgreSQL jsonb列插入JSON时转义字符致查询失效

问题背景 #

使用Python的SQLAlchemy库向PostgreSQL的jsonb列插入JSON字符串时，存入数据库的内容被包裹在字符串中且双引号被转义，导致无法通过JSON键值匹配进行查询。

原实现代码 #

生成JSON字符串的代码 #

survey_data_series = scto_df.loc[int(i)]

survey_data_json_string = json.dumps(survey_data_series.to_dict())

插入数据库的代码 #

def add_record(self, table_name, record_data):
    record = getattr(self, table_name)(**record_data)
    self.s.add(record)
    self.s.flush()
    return(record.id) 

现象对比 #

• 日志打印的JSON格式正常：

{"completiondate": "Feb 8, 2022 10:49:29 AM", "submissiondate": "Feb 8, 2022 10:49:29 AM", "starttime": "Feb 8, 2022 10:37:17 AM", "endtime": "Feb 8, 2022 10:49:21 AM", "deviceid": "ac29d854b49a49be", "subscriberid": NaN}

• 数据库中实际存储的内容（被转义包裹）：

"{\"completiondate\": \"Feb 8, 2022 10:49:29 AM\", \"submissiondate\": \"Feb 8, 2022 10:49:29 AM\", \"starttime\": \"Feb 8, 2022 10:37:17 AM\", \"endtime\": \"Feb 8, 2022 10:49:21 AM\", \"deviceid\": \"ac29d854b49a49be\", \"subscriberid\": NaN}"

无法正常工作的查询语句 #

SELECT JSON_EXTRACT_PATH_TEXT(survey_data_json::json,'starttime') AS starttime
FROM "Suvita".survey_data_json;

SELECT survey_data_json -> 'starttime' AS starttime
FROM "Suvita".survey_data_json;

SELECT (survey_data_json -> 0)--> '\"starttime\"' AS starttime
FROM "Suvita".survey_data_json; --返回整个JSON字符串

SELECT * FROM "Suvita".survey_data_json where survey_data_json->>'deviceid'='ac29d854b49a49be';

解决方法 #

不再手动将字典转为JSON字符串，直接把字典对象传入SQLAlchemy的表构造函数，SQLAlchemy会自动将字典转换为符合jsonb列要求的格式存入数据库。

修改后的插入代码：

record=self.survey_data_json(surveyid=surveyid,survey_data_json=dict_object)
self.s.add(record)

感谢@snakecharmerb的帮助

内容的提问来源于stack exchange，提问作者Shweta