pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedJSONB text extraction of data containing tab character fails
2+ messages / 2 participants
[nested] [flat]
* JSONB text extraction of data containing tab character fails
@ 2024-02-22 15:54 DEVOPS, Dos (NHS ENGLAND - X26) <dos.devops@nhs.net>
2024-02-22 16:40 ` RE: JSONB text extraction of data containing tab character fails Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 1 reply; 2+ messages in thread
From: DEVOPS, Dos (NHS ENGLAND - X26) @ 2024-02-22 15:54 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: DEVOPS, Dos (NHS ENGLAND - X26) <dos.devops@nhs.net>
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/;
^ permalink raw reply [nested|flat] 2+ messages in thread
* RE: JSONB text extraction of data containing tab character fails
2024-02-22 15:54 JSONB text extraction of data containing tab character fails DEVOPS, Dos (NHS ENGLAND - X26) <dos.devops@nhs.net>
@ 2024-02-22 16:40 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 0 replies; 2+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-02-22 16:40 UTC (permalink / raw)
To: DEVOPS, Dos (NHS ENGLAND - X26) <dos.devops@nhs.net>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
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://urldefense.com/v3/__https:/support.nhs.net/article-categories/joining-nhsmail/__;!!GFE8dS6ac...;
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-02-22 16:40 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-22 15:54 JSONB text extraction of data containing tab character fails DEVOPS, Dos (NHS ENGLAND - X26) <dos.devops@nhs.net>
2024-02-22 16:40 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox