PostgreSQL 反转义 JSON 字符串

Question

我将尝试使用PostgreSQL 9.3中的新JSON功能，并正在寻找一个函数来反转义JSON，与to_json（anyelement）相反。

下面是一个示例JSON：

{"single_comment": "Fred said \"Hi.\"" , 
"comments_array": ["Fred said \"Hi.\"", "Fred said \"Hi.\"", "Fred said \"Hi.\""]}  

查询语句：

SELECT json_array_elements(json_column->'comments_array')

按照文档描述，返回一组JSON。

"Fred said \"Hi.\""
"Fred said \"Hi.\""
"Fred said \"Hi.\""

有没有办法对结果进行反转义，这样我就可以得到以下结果：

Fred said "Hi."
Fred said "Hi."
Fred said "Hi." 

在文档中我没有找到任何可以帮助我的函数。不幸的是，对我来说安装PLV8不是一个选择。非常感谢任何想法。

- Dan

---

“Err... isn't "Fred said \"Hi.\"" just another way of writing 'Fred said "Hi."'`?”这句话不就是另一种写法吗？ - Denis de Bernardy

你尝试使用json_each_text而不是json_array_elements了吗？ - Ihor Romanchenko

json_each_text适用于键值结构，我有一个JSON数组。 - Dan

json_array_elements_text（而不是json_array_elements）就是这么做的。也许在2013年还没有这个函数 :-) - Bergi

Answer 1

我在其他网站上找到了一些简单的东西: 源这里

如果链接失效，可以查看详情:

select (ingredients #>> '{}')::jsonb->>'cheese' from pizza;

按照以下步骤操作 ☝️。当您拥有一个包含 jsonb 列（ingredients）的表格（pizza），并且意外地在该列中放入了转义后的 JSON 字符串，而不是 JSON 对象时。该值可能看起来像这样：

"{\"cheese\": \"mozzarella\"}"

看到引号了吗？那只是 JSON 列中根级别的字符串。但你真正想要的看起来像这样：

{"cheese": "mozzarella"}

好了，但是我们已经犯了错误，所以我们需要编写迁移来修复数据。以下是我们的做法：

(ingredients #>> '{}')::jsonb

Postgres中的#>>运算符获取指定路径上的“JSON对象文本”( PostgreSQL：文档：9.3：JSON函数和运算符)。 在这里，我们传入一个空路径，表示希望Postgres以text值的形式给出根级别的未转义字符串。

然后，我们可以将该text值强制转换回JSON格式，如下所示：(代替text-value)::jsonb。 然后，我们就有了一个普通的、解析后的JSON对象，在该对象的根级别上可以选择字段。

- Michael

---

同样地，如果您存储了一个包含转义JSON的值的键的未转义JSON对象：select (ingredients ->> 'meats')::jsonb->>'left' from pizza; 例如，对于ingredients列的存储数据为：{ "meats": "{ \"left\": \"salami\", \"right\": \"guanciale\"}" } - evan.bovie

Answer 2

select t.comments->>0 from 
(select jsonb_array_elements(your_table.json_column->'comments_array') as comments
from your_table) as t;

- expert

Answer 3

我刚遇到了这个问题，这是我的解决方法。我创建了一个帮助函数，迭代数组并使用- & gt; & gt;运算符，使用下标获取文本值。如果有更好的方法，欢迎告诉我，因为这似乎有点笨重。

CREATE OR REPLACE FUNCTION json_text_array_to_pg_text_array(data json) returns text[] AS $$
DECLARE
    i integer;
    agg text[];
BEGIN
    FOR i IN 0..json_array_length(data)-1 LOOP
        agg := array_append(agg, data->>i);
    END LOOP;

    return agg;
END
$$ language plpgsql;

然后你可以做以下事情：

test=# select json_text_array_to_pg_text_array('[ "hello","the\"re","i''m", "an", "array" ]'::json);
 json_text_array_to_pg_text_array 
----------------------------------
 {hello,"the\"re",i'm,an,array}
(1 row)

如果你不想直接处理数组，也可以让函数只返回一组文本：

CREATE OR REPLACE FUNCTION json_text_array_to_row(data json) returns setof text AS $$
DECLARE
    i integer;
BEGIN
    FOR i IN 0..json_array_length(data)-1 LOOP
        return next data->>i;
    END LOOP;
    return;
END
$$ language plpgsql;

然后执行以下操作：

test=# select json_text_array_to_row('{"single_comment": "Fred said \"Hi.\"" ,"comments_array": ["Fred said \"Hi.\"", "Fred said \"Hi.\"", "Fred said \"Hi.\""]}'::json->'comments_array');
 json_text_array_to_row 
------------------------
 Fred said "Hi."
 Fred said "Hi."
 Fred said "Hi."
(3 rows)

- bhirt

Answer 4

我只需稍微修改 JSON 就成功地实现了结果：

{"comments_array": [{"comment": "Fred said \"Hi.\""}, {"comment": "Fred said \"Hello.\""}]}

现在我们使用对象数组而不是字符串数组，以下查询按照我的要求工作：

SELECT (json_array_elements(json_column->'comments_array'))->>'comment'

目前这个方案可以满足我的需求，但如果有人知道如何从字符串数组中实现输出，请分享一下 :)

- Dan