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
39
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
39
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)