如何将JSON字符串转换为文本？

Question

JSON值可以由字符串值组成。例如：

postgres=# SELECT to_json('Some "text"'::TEXT);
     to_json
-----------------
 "Some \"text\""

如何将该字符串提取为Postgres文本值？ ::TEXT不起作用。它返回带引号的JSON，而不是原始字符串。

postgres=# SELECT to_json('Some "text"'::TEXT)::TEXT;
     to_json
-----------------
 "Some \"text\""

P.S. 我正在使用 PostgreSQL 9.3

- e79ene

---

https://dev59.com/a3jZa4cB1Zd3GeqPZQ5p 可能有帮助。也可能没有。 - Mike Sherrill 'Cat Recall'

字符串数组的类似问题，https://dev59.com/P6Pia4cB1Zd3GeqP1qao - Peter Krauss

相关问题: 如何将Postgres json(b)转换为整数？，转换为浮点数？，转换为布尔值？ - Bergi

Answer 1

使用#>>运算符在9.4.4版本中适用于我：

select to_json('test'::text) #>> '{}';

用于表格列：

select jsoncol #>> '{}' from mytable;

- Ian Timothy

---

在Postgres 9.4中，似乎是最简单的解决方案。但是在9.3中不起作用。 - e79ene

@hasen在这里说，OP表示他正在尝试从JSON值中提取文本，并且to_json（...）只是一种创建JSON值以在一个简短的一行语句中使用作为示例的简单方法。如果您正在查询表格，则肯定会将其替换为JSON列的名称。此外，为了澄清一个可能令人困惑的地方，您的转换(...) :: text是冗余的，因为“＃>>”运算符根据定义返回文本（并且是使用该运算符的原因）。您可以保留括号，但放弃转换::text。 - Ian Timothy

有人能解释一下 #>> 和 '{}' 是做什么的吗？我无法理解这个问题，而且这两个术语都不容易在谷歌上找到相关信息。这个答案确实解决了我的问题，但我想知道为什么。 - valadil

@valadil #>> 运算符的文档在这里。 - Ian Timothy

在这种情况下，存在一个顶级或根JSON对象text。它看起来可能像一个字符串，但实际上是一个JSON对象。要将该对象从JSON转换为文本，请使用#>>运算符。但该运算符需要您指定一个路径。到达根对象的路径为{}。因此，SELECT '"test"'::jsonb #>> '{}'的意思是“获取根路径处的对象并将其转换为文本”。 - Ian Timothy

Answer 2

在PostgreSQL中，没有办法将标量JSON对象进行分解。因此，正如您所指出的那样，

select  length(to_json('Some "text"'::TEXT) ::TEXT);

是15，

把JSON转换为一个JSON元素的数组，然后使用->>提取该元素。

select length( array_to_json(array[to_json('Some "text"'::TEXT)])->>0 );

将返回11。

- Robert M. Lefkowitz

---

很遗憾，据我所知json_extract_path_text()无法引用根元素。 - Erwin Brandstetter

有趣的是，在2012年API设计阶段显然有一个头脑风暴讨论，提出了一个名为 from_json 的函数，但并未实现。https://wiki.postgresql.org/wiki/JSON_API_Brainstorm - nikola

这已经过时了。请查看其他答案，以获取使用当前版本的PostgreSQL更简单的解决方案。 - yankee

Answer 3

Mr. Curious同样很好奇。除了#>> '{}'运算符之外，在9.6+版本中，人们可以使用->>运算符获取jsonb字符串的值:

select to_jsonb('Some "text"'::TEXT)->>0;
  ?column?
-------------
 Some "text"
(1 row)

如果有一个 JSON 值，则解决方法是首先将其转换为 JSONB：

select to_json('Some "text"'::TEXT)::jsonb->>0;
  ?column?
-------------
 Some "text"
(1 row)

- Mr. Curious

Answer 4

->> 对我有效。

PostgreSQL 版本：

<postgres.version>11.6</postgres.version>

查询：

select object_details->'valuationDate' as asofJson, object_details->>'valuationDate' as asofText from MyJsonbTable;

输出：

  asofJson       asofText
"2020-06-26"    2020-06-26
"2020-06-25"    2020-06-25
"2020-06-25"    2020-06-25
"2020-06-25"    2020-06-25

- curious_soul

---

谢谢指出，我已经更正了上面的版本。 - curious_soul

原问题是如何获取JSON字符串的文本值（没有对象键）。这个答案只是在使用键时->和->>之间的区别。请参见此答案或此答案。 - Ian Timothy

这个是正确的！ - Anggi Permana Harianja

Answer 5

我也面对过这个挑战。如果你有一个“多选”json，你可以使用以下代码将列表转换为单独的文本元素：

```sql SELECT jsonb_array_elements_text(yourjsoncolumn) FROM yourtable ```

如果我按照上面的其他建议操作，仍然会得到带引号的json值。

- SAMBUKI

---

yourjsoncolumn ->> 0对你来说肯定有效，但只返回数组的第一个值，而不是每个元素。jsonb_array_elements(yourjsoncolumn) #>> '{}'也可以工作，但显然更丑陋 - 如果你有一个JSON数组，jsonb_array_elements_text绝对是最好的选择。 - undefined

Answer 6

一种简单的方法：

SELECT  ('[' || to_json('Some "text"'::TEXT) || ']')::json ->> 0;

只需将JSON字符串转换为JSON列表即可

- Zhemin Zhou