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 1qd7zz-002HA9-UY for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Sep 2023 11:44:08 +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 1qd7zy-00AhMi-9o for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Sep 2023 11:44:06 +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 1qd7zx-00AhMQ-RX for pgsql-hackers@lists.postgresql.org; Mon, 04 Sep 2023 11:44:05 +0000 Received: from mail-oo1-xc30.google.com ([2607:f8b0:4864:20::c30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qd7zu-002for-KJ for pgsql-hackers@postgresql.org; Mon, 04 Sep 2023 11:44:04 +0000 Received: by mail-oo1-xc30.google.com with SMTP id 006d021491bc7-573429f5874so859700eaf.0 for ; Mon, 04 Sep 2023 04:44:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1693827842; x=1694432642; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=B/F8Q62O7wagKMqlzOSnKUfj5wiYb+im/ZgqwUFae7o=; b=QkEW/jtxxrkRLyrOj81DiHMjlhxYRJ1UrFnaFuNGmCQF3K/AuvsXE2tWyrxZ60nFWa 9KFzOE1AkQATxKrOcrRs5wgs6YGW/2jm8dd1ikiwdeG2nUAgJGJ7ahx5TNeydCB0EwoB Z7I8QkJ/p5OG5M6/y+giRBfqvCbG1xKJMkOLyPva4xKe931WxgVpSTxFrmJN4FIJNAy6 rlvgpRIA62cdIENVfKJZf/NZxJ7Ml4UnXrFf4M0bo2tQrOUi8YYd1TMUr7L8mrvLg7E+ R1dqdoABq9bZf1AE+/C0a1U7aEeScMDGdRlqOLr13uFI7Z+5VSjy3LCiwMkXX5iCoZc7 0YbA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1693827842; x=1694432642; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=B/F8Q62O7wagKMqlzOSnKUfj5wiYb+im/ZgqwUFae7o=; b=OSkrjPd9MRqHWayRMESKyrkfE4eRo06geN1dkvXdHHlzbRE1TWITbji0HgF/yJkyOh QBuDHRaBPlcMdrtTEQ2/Ow3wbSQ02oavJFeH/xYklihs9pII81J3tCHRKvur34vvaUh0 moXgazgZhQAFGWl/pN9Ds4yDM2dFSqdaO9iYjGNxC8miI9TY7JxmZRntudBkNjTPrAv+ r+p0gYvZvQm/Wr6ozGuUnSZ/kiW7xvkWHNnmh4TJYJin9RuRfGXlRmdFitIT48s9JYfU F7yWFMrYQSfl8PMUSfBz07lqUOyND9Ur5MkJ9s58k4KsuWo3+vLXPsZu64wn4DojrVS9 VnwQ== X-Gm-Message-State: AOJu0YyXfRJ9DKYLiFDtNPfybantAdUz7ybEOlbMw7xLIRPrl0qKPZFk YRDXbE2YyTZRdQx+1qH4Ha2i1VywZyRNDVSr/DQ= X-Google-Smtp-Source: AGHT+IH2KYZmJvaJ4wxUfR5etdsqJuiW2co7rR/CqnEExtwhIkTMotHVxNdZh0tudSLeeRSVSpwvQJnShFEsR/miW2E= X-Received: by 2002:a4a:919b:0:b0:571:28a7:bcca with SMTP id d27-20020a4a919b000000b0057128a7bccamr8167497ooh.1.1693827841738; Mon, 04 Sep 2023 04:44:01 -0700 (PDT) MIME-Version: 1.0 References: <3507485.1691090027@sss.pgh.pa.us> <900892914fdc3f477b101d699efb40e0@anastigmatix.net> <903341.1692022214@sss.pgh.pa.us> <4b97f1a1dd9b6e45443d24870d3be698@anastigmatix.net> <111272f2dc112c7becdd35ad89f6b935@anastigmatix.net> <5138c6b5fd239e7ce4e1a4e63826ac27@anastigmatix.net> <369543439e988ae43f0a6307500b27c4@anastigmatix.net> <5955e93347a7e3b1612cf7e129ae6d04@anastigmatix.net> In-Reply-To: From: Andy Fan Date: Mon, 4 Sep 2023 19:43:50 +0800 Message-ID: Subject: Re: Extract numeric filed in JSONB more effectively To: jian he Cc: Chapman Flack , Pavel Stehule , Tom Lane , pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000d14dbc0604870625" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d14dbc0604870625 Content-Type: text/plain; charset="UTF-8" Hi Jian, SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type > = 'object'; > -ERROR: cannot cast jsonb string to type integer > +ERROR: unknown jsonb type: 1125096840 > Thanks for the report! The reason is I return the address of a local variable. jsonb_object_field_start(PG_FUNCTION_ARGS) { JsonbValue *v; JsonbValue vbuf; v = getKeyJsonValueFromContainer(&jb->root, VARDATA_ANY(key),\ VARSIZE_ANY_EXHDR(key), &vbuf); PG_RETURN_POINTER(v); } Here the v points to vbuf which is a local variable in stack. I'm confused that why it works on my local machine and also works in the most queries in cfbot, the fix is below v = getKeyJsonValueFromContainer(&jb->root, VARDATA_ANY(key),\ VARSIZE_ANY_EXHDR(key), NULL); I will send an updated version soon. -- Best Regards Andy Fan --000000000000d14dbc0604870625 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Jian,

=C2=A0SELECT (test_jso= n -> 'field1')::int4 FROM test_jsonb WHERE json_type
=3D 'object';
-ERROR:=C2=A0 cannot cast jsonb string to type integer
+ERROR:=C2=A0 unknown jsonb type: 1125096840

Thanks for the report!=C2=A0 The reason is I return the address of a = local variable.=C2=A0

jsonb_object_field_start(PG_FUNCT= ION_ARGS)
{

=C2=A0 =C2=A0 JsonbValue =C2=A0*v;
=C2=A0 =C2=A0 J= sonbValue =C2=A0vbuf;
=C2=A0 =C2=A0 v =3D getKeyJsonValueFromContainer(&= amp;jb->root,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0VARDATA_ANY(key),\
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0VARSIZE_ANY_EXHDR(key),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0&vbuf);
=C2=A0 =C2=A0 PG_RETURN_POINTER(v);
}=

Here = the v points to vbuf which is a local variable in stack.=C2=A0 I'm conf= used
that why it works on my local machine = and also works in the most queries
in cfbot= , the fix is below

=C2=A0 =C2=A0 v =3D getKeyJsonValueFromContainer(&jb->r= oot,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0VARDATA_ANY(key),\
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0VARSIZE_ANY_EXHDR(key),
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0NULL);


I will send an updated version soon.=C2=A0

--
Best Regards
Andy Fan
--000000000000d14dbc0604870625--