I have the following function in 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";
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?
Quotes and double quotes should be escaped using \.
The LTRIM() function removes all characters, spaces by default, from the beginning of a string. The RTRIM() function removes all characters, spaces by default, from the end of a string. The BTRIM function is the combination of the LTRIM() and RTRIM() functions.
To remove double quotes just from the beginning and end of the String, we can use a more specific regular expression: String result = input. replaceAll("^\"|\"$", ""); After executing this example, occurrences of double quotes at the beginning or at end of the String will be replaced by empty strings.
Ensure your Text qualifier is set to " – this will remove the quotes around your fields. As we have no headers in the file, untick the “Column names in the first data row” checkbox. Preview the file to ensure it looks OK.
The ->
operator returns a json
result. Casting it to text
leaves it in a 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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With