Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rdC7L-004LAf-4B for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Feb 2024 16:40:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rdC7J-00CcGk-Ng for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Feb 2024 16:40:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rdC7I-00CcFX-RP for pgsql-bugs@lists.postgresql.org; Thu, 22 Feb 2024 16:40:13 +0000 Received: from mx0a-000e5c14.pphosted.com ([148.163.133.209]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rdC7E-000Sgs-8d for pgsql-bugs@lists.postgresql.org; Thu, 22 Feb 2024 16:40:11 +0000 Received: from pps.filterd (m0206226.ppops.net [127.0.0.1]) by mx0a-000e5c14.pphosted.com (8.17.1.19/8.17.1.19) with ESMTP id 41MFb8RE028167; Thu, 22 Feb 2024 10:40:08 -0600 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=express-scripts.com; h=from : to : subject : date : message-id : references : in-reply-to : content-type : mime-version; s=pod140124231234; bh=NB77xSVhh42RuBENHN/exUcxfdcMy/8d7kMQ9X5BH1k=; b=l6wQnrta7KkM2/5NZ6MhghE+U3QksgcaajVZ8ByEeA4iUl321QYDHsVvdXXYa8OwUcIl Jkb4ctX06/FQ1ZPBmJfWMOM5O9+sN35WkKyQ3UUy+DwbRzTNy4XEFwkgNPEMkw/5M8of TzxEsgLQVyF5CUO9dPlVfj1d5L/ZYFrz2V6A+5wLV+ecyshYFqP1tO7zQTL/Numphfu5 wCHPP+D+mqjgPoA61N2bPWICintetgvb+9kMiWO/+RAJetNOBqaEJ6hdIWtPH22jFLOr rAy1J3NbWQJdhACmDodHDOKi/JcdPScfptJYCBMNf3Q4pu0LEAsykSSr8xK98NsjBqrj LA== Received: from eiaappxp0010.gid.prd.globalcore.com (lyncav03.cigna.com [170.48.19.149]) by mx0a-000e5c14.pphosted.com (PPS) with ESMTPS id 3wd56pv0h2-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Thu, 22 Feb 2024 10:40:07 -0600 Received: from cvlappxp20748.internal.cigna.com (HELO PS2PW1012185.accounts.root.corp) ([10.36.52.78]) by EIAAPPXP0010.gid.prd.globalcore.com with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 22 Feb 2024 16:40:07 +0000 Received: from PS2PW1012186.accounts.root.corp (10.221.225.154) by PS2PW1012185.accounts.root.corp (10.221.225.153) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1258.27; Thu, 22 Feb 2024 11:40:05 -0500 Received: from PS2PW1012186.accounts.root.corp ([fe80::9fec:fffb:1381:33ad]) by PS2PW1012186.accounts.root.corp ([fe80::9fec:fffb:1381:33ad%6]) with mapi id 15.02.1258.027; Thu, 22 Feb 2024 11:40:05 -0500 From: "Wetmore, Matthew (CTR)" To: "DEVOPS, Dos (NHS ENGLAND - X26)" , "pgsql-bugs@lists.postgresql.org" Subject: RE: JSONB text extraction of data containing tab character fails Thread-Topic: JSONB text extraction of data containing tab character fails Thread-Index: AQHaZaDdiFDv3Jqk0EGWcZMYcAL/8LEWjyHA Date: Thu, 22 Feb 2024 16:40:05 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.222.252.1] x-proofpoint-routing: 000e5c14 Content-Type: multipart/alternative; boundary="_000_a528058c1a314b8bb5b5d9d1e0acae8cexpressscriptscom_" MIME-Version: 1.0 X-CFilter-Loop: Reflected X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1011,Hydra:6.0.619,FMLib:17.11.176.26 definitions=2024-02-22_12,2024-02-22_01,2023-05-22_02 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_a528058c1a314b8bb5b5d9d1e0acae8cexpressscriptscom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I'm going through this myself. 1. Data (varchar) column data type should be a json(b) data type colum= n, not Varchar, that is causing this since the unescaped / is a valid chara= cter 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 g= otten 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 s= omething) From: DEVOPS, Dos (NHS ENGLAND - X26) Sent: Thursday, February 22, 2024 7:54 AM To: pgsql-bugs@lists.postgresql.org Cc: DEVOPS, Dos (NHS ENGLAND - X26) Subject: [EXTERNAL] JSONB text extraction of data containing tab character = fails Relevant table and column structure cgsh =3D=3D=3D=3D=3D 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 inten= ded recipient please: i) inform the sender that you have received the message in error before del= eting 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 da= ta and other sensitive information with NHSmail and other accredited email = services. For more information and to find out how you can switch visit Joining NHSma= il - NHSmail Support --_000_a528058c1a314b8bb5b5d9d1e0acae8cexpressscriptscom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I’m going through this myself.<= o:p>

 

1.   = ;   Data (varchar) column data ty= pe 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 h= ave been able to fix at time of insert.

 

3.   = ;   Rewrite query to not use ::js= on->>’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 cha= racter fails

 

Relevant table and column structure

&= nbsp;

cgsh<= /o:p>

=3D=3D=3D=3D=3D

id (int)

data (varchar) --contains json data

&= nbsp;

Relevant data in the table

 

{code}=

1, {"name":"item1","element":&quo= t;record with a tab char\t"}

{/code}

 

Query

 

{code}

select id, data::jsonb->>'element' from cgsh<= /o:p>

{/code}

&= nbsp;

Expected behaviour

&= nbsp;

Return=

{code}=

1, record with a tab char\t<= /p>

{/code}

&= nbsp;

Actual behaviour

&= nbsp;

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}

&= nbsp;



***************************************************************************= *********** ******************************

This message may contain confidential information. If you are not the inten= ded recipient please:
i) inform the sender that you have received the message in error before del= eting 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 da= ta 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

--_000_a528058c1a314b8bb5b5d9d1e0acae8cexpressscriptscom_--