I’m going through this myself.
1. Data (varchar) column data type should be a json(b) data type column, not Varchar, that is causing this since the unescaped / is a valid character for Varchar and when you cast to json, the unescaped / become invalid.
2. To get best results from json data, store as json, you would have gotten the error in insert and would have been able to fix at time of insert.
3. Rewrite query to not use ::json->>’element’ (select substring or something)
From: DEVOPS, Dos (NHS ENGLAND - X26) <dos.devops@nhs.net>
Sent: Thursday, February 22, 2024 7:54 AM
To: pgsql-bugs@lists.postgresql.org
Cc: DEVOPS, Dos (NHS ENGLAND - X26) <dos.devops@nhs.net>
Subject: [EXTERNAL] JSONB text extraction of data containing tab character fails
Relevant table and column structure
data (varchar) --contains json data
Relevant data in the table
1, {"name":"item1","element":"record with a tab char\t"}
select id, data::jsonb->>'element' from cgsh
1, record with a tab char\t
SQL Error [22P02]: ERROR: invalid input syntax for type json
Detail: Character with value 0x09 must be escaped.
Where: JSON data, line 1: ...
************************************************************************************** ******************************
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