JSONB text extraction of data containing tab character fails

From: "DEVOPS, Dos (NHS ENGLAND - X26)" <dos(dot)devops(at)nhs(dot)net>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: "DEVOPS, Dos (NHS ENGLAND - X26)" <dos(dot)devops(at)nhs(dot)net>
Subject: JSONB text extraction of data containing tab character fails
Date: 2024-02-22 15:54:24
Message-ID: CWXP265MB1879A30E1D05FCC9113FA3B593562@CWXP265MB1879.GBRP265.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Relevant table and column structure

cgsh
=====
id (int)
data (varchar) --contains json data

Relevant data in the table

{code}
1, {"name":"item1","element":"record with a tab char\t"}
{/code}

Query

{code}
select id, data::jsonb->>'element' from cgsh
{/code}

Expected behaviour

Return
{code}
1, record with a tab char\t
{/code}

Actual behaviour

Return
{code}
SQL Error [22P02]: ERROR: invalid input syntax for type json
Detail: Character with value 0x09 must be escaped.
Where: JSON data, line 1: ...
{/code}

************************************************************************************** ******************************

This message may contain confidential information. If you are not the intended recipient please:
i) inform the sender that you have received the message in error before deleting it; and
ii) do not disclose, copy or distribute information in this e-mail or take any action in relation to its content (to do so is strictly prohibited and may be unlawful).
Thank you for your co-operation.

NHSmail is the secure email, collaboration and directory service available for all NHS staff in England. NHSmail is approved for exchanging patient data and other sensitive information with NHSmail and other accredited email services.

For more information and to find out how you can switch visit Joining NHSmail - NHSmail Support<https://support.nhs.net/article-categories/joining-nhsmail/>

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Wetmore, Matthew (CTR) 2024-02-22 16:40:05 RE: JSONB text extraction of data containing tab character fails
Previous Message PG Bug reporting form 2024-02-22 14:43:18 BUG #18359: autovacuum stopped vacuuming user tables, focusing on just pg_auth_members repeatedly