Re: Extract numeric [field] in JSONB more effectively

On Wed, Aug 9, 2023 at 4:30 AM Chapman Flack <chap(at)anastigmatix(dot)net> wrote:
>
> Hi,
>
> Looking at the most recent patch, so far I have a minor
> spelling point, and a question (which I have not personally
> explored).
>
> The minor spelling point, the word 'field' has been spelled
> 'filed' throughout this comment (just as in the email subject):
>
> + /*
> + * Simplify cast(jsonb_object_filed(jsonb, filedName) as
type)
> + * to jsonb_object_field_type(jsonb, filedName,
targetTypeOid);
> + */
>
> The question: the simplification is currently being applied
> when the underlying operation uses F_JSONB_OBJECT_FIELD.
> Are there opportunities for a similar benefit if applied
> over F_JSONB_ARRAY_ELEMENT and/or F_JSONB_EXTRACT_PATH?
>
> Regards,
> -Chap

Based on most recent patch
in jsonb_object_field_type function, I made some changes, need to
include <unistd.h>. just created a C function, but didn't rebuild. then
compare it with the "numeric"(jsonb) function. overall it's fast.

some changes I made in jsonb_object_field_type.

uint32 i;
char *endptr;
if (JB_ROOT_IS_OBJECT(jb))
v = getKeyJsonValueFromContainer(&jb->root,
VARDATA_ANY(key),
VARSIZE_ANY_EXHDR(key),
&vbuf);
else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb)) /* scalar element
is pseudo-array */
{
errno = 0;
char *src = text_to_cstring(key);
i = (uint32) strtoul(src, &endptr, 10);

if (endptr == src || *endptr != '\0' || errno != 0)
{
elog(ERROR,"invalid input syntax when convert to integer:");
}
// i boundary index checked inside.
v = getIthJsonbValueFromContainer(&jb->root,i);
}
else if (JB_ROOT_IS_SCALAR(jb))
{
if (!JsonbExtractScalar(&jb->root, &vbuf) || vbuf.type != jbvNumeric)
cannotCastJsonbValue(vbuf.type, "numeric");
v = &vbuf;
}
else
PG_RETURN_NULL();
---------------------------------------
The following query will return zero rows. but jsonb_object_field_type will
be faster.
select jsonb_object_field_type('[1.1,2.2]'::jsonb,'1', 1700),
jsonb_object_field_type('{"1":10.2}'::jsonb,'1', 1700),
jsonb_object_field_type('10.2'::jsonb,'1', 1700)
except
select "numeric"(('[1.1,2.2]'::jsonb)[1]),
"numeric"('{"1":10.2}'::jsonb->'1'),
"numeric"('10.2'::jsonb);

how to glue it as a support function, or make it more generic needs extra
thinking.