在postgresql中处理Unicode序列

Question

我在我的PostgreSQL数据库(9.4.1)中有一些JSON数据存储在一个JSON（非JSONB）列中。其中一些JSON结构包含其属性值中的Unicode序列。例如：

{"client_id": 1, "device_name": "FooBar\ufffd\u0000\ufffd\u000f\ufffd" }

当我尝试查询这个JSON列时（即使我并没有直接尝试访问device_name属性），我会收到以下错误信息：

 

错误：不支持的Unicode转义序列
  详细信息：\u0000无法转换为文本。

您可以通过在PostgreSQL服务器上执行以下命令来重新创建此错误：

select '{"client_id": 1, "device_name": "FooBar\ufffd\u0000\ufffd\u000f\ufffd" }'::json->>'client_id'

我能理解这个错误 - 在文本结果中没有办法表示Unicode序列NULL。

有没有什么方法可以查询相同的JSON数据，而不必对输入数据进行“消毒”处理？由于这些JSON结构经常更改，因此扫描特定属性（在这种情况下为device_name）将不是一个好的解决方案，因为很容易有其他可能包含类似数据的属性。

---

经过进一步调查，似乎该行为是版本9.4.1的新行为，如更改日志中所述：

...因此，在需要将其转换为反斜杠形式时，json值现在也会拒绝\u0000。 只要不对值进行任何处理，此更改不会破坏在json列中存储\u0000的能力...

这真的是意图吗？降级到9.4.1之前的版本是否可行？

---

_{顺便说一句，此属性来自客户移动设备的名称 - 是用户将此文本输入设备的。用户如何插入NULL和REPLACEMENT CHARACTER值的？！}

- Lix

---

仅供参考，这不仅适用于 SELECT - 我在 9.5 和 9.6 上使用 SQL UPDATE 语句时也遇到了同样的问题。 - Per Lundberg

我在 PostgreSQL 10.1 上运行了您提供的 SELECT，编译器为 Visual C++ build 1800，32 位，结果返回了相同的错误。 - Daniel L. VanDenBosch

最近我在开发一个与PostgreSQL交互的应用时遇到了同样的问题，但是我通过将这个序列替换为空字符串来解决了客户端的问题（在Python中看起来像这样：json_string.replace("\u0000", "")）。 - DataGreed

Answer 1

\u0000是字符串中唯一不合法的Unicode代码点。我认为除了对字符串进行清理处理外，别无他法。

由于json只是一种特定格式的字符串，您可以使用标准字符串函数而不必担心JSON结构。 一个一行代码的清理程序可以删除该代码点：

SELECT (regexp_replace(the_string::text, '\\u0000', '', 'g'))::json;

但您也可以插入您喜欢的任何字符，这将非常有用，如果零代码点被用作某种分隔符。

还要注意存储在数据库中和向用户呈现的内容之间的微妙差异。您可以将代码点存储在JSON字符串中，但必须在将该值处理为json数据类型之前对其进行预处理为其他字符。

- Patrick

---

感谢您的回复，@patrick。我刚刚对我的帖子进行了编辑。在版本3.9.1中，同样的问题没有重现，因此某个阶段它被认为是一个有效的字符串。我不太喜欢对进入系统的每个数据使用如此广泛的正则表达式处理，但至少如果我陷入困境并且没有其他办法，我将能够使用它:)所以，谢谢您。 - Lix

没错。在 PostgreSQL 字符串中，空字节不合法。零码点也一样。 - Craig Ringer

@CraigRinger - 不可能只是在字符串响应中获得一个字面上的 "\u0000" 吗？ - Lix

在使用9.6版本时，您也可以只使用replace而不是正则表达式版本，例如：select replace('{ "a": "null \u0000 word \u0000 escape" }', '\u0000', '')::json->> 'a' as succeeds; - mark

JSON字符串中可以完全接受空值，这是否意味着PostgreSQL的JSON实现有缺陷？因为它无法处理每种JSON格式。 - Rahly

Answer 2

Patrick的解决方案对我来说并没有立即奏效。无论如何，总是会抛出错误。然后我进行了更多的研究，并能够编写一个小的自定义函数来解决我的问题。

首先，我通过编写以下代码可以重现错误：

select json '{ "a":  "null \u0000 escape" }' ->> 'a' as fails

然后我添加了一个自定义函数，我在查询中使用了它：

CREATE OR REPLACE FUNCTION null_if_invalid_string(json_input JSON, record_id UUID)
  RETURNS JSON AS $$
DECLARE json_value JSON DEFAULT NULL;
BEGIN
  BEGIN
    json_value := json_input ->> 'location';
    EXCEPTION WHEN OTHERS
    THEN
      RAISE NOTICE 'Invalid json value: "%".  Returning NULL.', record_id;
      RETURN NULL;
  END;
  RETURN json_input;
END;
$$ LANGUAGE plpgsql;

调用此函数，请按照以下步骤操作。您不应该收到错误信息。

select null_if_invalid_string('{ "a":  "null \u0000 escape" }', id) from my_table

而这应该如预期地返回json：

select null_if_invalid_string('{ "a":  "null" }', id) from my_table

- Hendrik

---

我也遇到了一些问题，就像Patric的解决方案一样，但这是因为我在查询中使用它太晚了 - 你需要尽快修复你的数据，因为似乎即使像json_array_elements这样的操作也会失败。所以只需将你正在处理的整个json转换为text，然后替换并转换回json，然后查询应该可以正常工作。 - icl7126

Answer 3

我找到了适合我的解决方案

SELECT (regexp_replace(the_string::text, '(?<!\\)\\u0000', '', 'g'))::json;

请注意匹配模式'(?<!\)\u0000'。

- Antonb73

Answer 4

您可以使用SQL修复所有条目，如下所示：

update ___MY_TABLE___ 
set settings = REPLACE(settings::text, '\u0000', '' )::json
where settings::text like '%\u0000%'

- knagode

Answer 5

仅供网络搜索者参考：

这不是对确切问题的解决方案，但在某些类似情况下，如果您只是不想在您的JSON中包含空字节的数据集，则可以添加以下内容：

AND json NOT LIKE '%\u0000%'

在你的WHERE语句中。

你也可以使用REPLACE SQL语法来清理数据:

REPLACE(source_field, '\u0000', '' );

- rubo77

---

这将过滤掉所有包含nullbyte的结果。我（大约5年前呵呵）当时很想获取数据本身而不包括nullbyte。这基本上是一个排除那些字符，而不需要对传入数据进行净化的问题。回头看，最好还是清理数据并确保不插入无效的json比较好。 - Lix

非常好，这正是我所需要的。谢谢！ - Martin Jambon