从PostgreSQL中的函数返回中删除双引号

I have the following function in PostgreSQL

我在PostgreSQL中有以下功能

CREATE OR REPLACE FUNCTION public.translatejson(JSONB, TEXT)
RETURNS TEXT
AS
$BODY$
   SELECT ($1->$2)::TEXT
$BODY$
LANGUAGE sql STABLE;

When I execute it I receive the values surrounded by double quotes. For example:

当我执行它时，我收到双引号括起来的值。例如：

SELECT id, translatejson("title", 'en-US') AS "tname" FROM types."FuelTypes";

SELECT id，translatejson（“title”，“en-US”）AS“tname”FROM types。“FuelTypes”;

in return I get a table like this

作为回报，我得到一张这样的桌子

-------------------
| id | tname      |
-------------------
| 1  | "gasoline" |
| 2  | "diesel"   |
-------------------

The values in the 'title' column are in JSON format: { "en-US":"gasoline", "fr-FR":"essence" }. How I can omit the double quotes to return just the string of the result?

“标题”列中的值采用JSON格式：{“en-US”：“汽油”，“fr-FR”：“精华”}。我如何省略双引号只返回结果的字符串？

1 个解决方案

#1

The -> operator returns a json result. Casting it to text leaves it in a json reprsentation.

- >运算符返回json结果。将其转换为文本会将其留在json reprsentation中。

The ->> operator returns a text result. Use that instead.

- >>运算符返回文本结果。改用它。

test=> SELECT '{"car": "going"}'::jsonb -> 'car';
 ?column? 
----------
 "going"
(1 row)

test=> SELECT '{"car": "going"}'::jsonb ->> 'car';
 ?column? 
----------
 going
(1 row)

#1