printing JsonbPair values of input JSONB on server side?
Hi,
I am trying to write a PostgreSQL (11.2) server side function to read the
key-value pairs of an input JSONB object. The code I have assembled so far
(by mimicking existing code I can find) is below. (This is the closest
thing I can find/write, and I couldn't find any documentation by the way.)
PG_FUNCTION_INFO_V1(print_kv_pair);
Datum
print_kv_pair(PG_FUNCTION_ARGS)
{
Jsonb *jb1 = PG_GETARG_JSONB_P(0);
JsonbIterator *it1;
JsonbValuev1;
JsonbIteratorToken r1;
JsonbParseState *state = NULL;
if (jb1 == NULL)
PG_RETURN_JSONB_P(jb1);
if (!JB_ROOT_IS_OBJECT(jb1))
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("Can only take objects")));
elog(NOTICE, "print_kv_pair(): ok0");
it1 = JsonbIteratorInit(&jb1->root);
r1 = JsonbIteratorNext(&it1, &v1, false);
if (r1 != WJB_BEGIN_OBJECT)
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("Iterator was not an object")));
elog(NOTICE, "print_kv_pair(): ok1");
pushJsonbValue(&state, r1, NULL);
r1 = JsonbIteratorNext(&it1, &v1, false);
JsonbValue *object = &v1;
elog(NOTICE, "print_kv_pair(): ok2");
Assert(object->type == jbvObject);
elog(NOTICE, "print_kv_pair(): ok3, nPairs = %d",
object->val.object.nPairs);
//iterating through key-value pairs
JsonbPair *ptr;
for (ptr = object->val.object.pairs;
ptr - object->val.object.pairs < object->val.object.nPairs;
ptr++)
{
//Problem line!!!
//elog(NOTICE, "print_kv_pair(): k = %s, v = %s",
ptr->key.val.string.val, numeric_out(ptr->value.val.numeric));
}
elog(NOTICE, "print_kv_pair(): ok4");
PG_RETURN_JSONB_P(JsonbValueToJsonb(object));
}
---
For example, for sample input:
select print_kv_pair('{"a":1, "b": 2}');
, I expect it to print something like:
k = "a", v = 1
k = "b", v = 2
However, the code crashes the PostgreSQL server, when I try to print (at
the `Problem line!!!`).
*Can someone explain how to fix the code and correctly iterate through the
key-value pairs?*
Sample output with problem line disabled:
=> select print_kv_pair('{"a":1, "b": 2}');
NOTICE: print_kv_pair(): ok0
NOTICE: print_kv_pair(): ok1
NOTICE: print_kv_pair(): ok2
NOTICE: print_kv_pair(): ok3, nPairs = 1
NOTICE: print_kv_pair(): ok4
print_kv_pair
---
"a"
(1 row)
One additional question:
Without documentation, I don't understand what these two lines do, and
whether they should be deleted.
pushJsonbValue(&state, r1, NULL);
r1 = JsonbIteratorNext(&it1, &v1, false);
The doxygen page says that there is no reverse of `JsonbValueToJsonb`
(i.e. `
JsonbToJsonbValue`). But I guess that's exactly what's needed here (to
extract the JsonbValue from the JSONB object). I don't quite get the work
around hinted there.
Thanks in advance for your help,
Re: printing JsonbPair values of input JSONB on server side?
Thanks a lot for the suggestions. I changed the code below (with `pnstrdup`
and `DatumGetCString`).
But the code still crashes at the two problem lines (either one alone
crashes the server). The input is:
select print_kv_pair('{"a":1, "b": 2}');
Any further insight?
-- modified code --
PG_FUNCTION_INFO_V1(print_kv_pair);
Datum
print_kv_pair(PG_FUNCTION_ARGS)
{
Jsonb *jb1 = PG_GETARG_JSONB_P(0);
JsonbIterator *it1;
JsonbValuev1;
JsonbIteratorToken r1;
JsonbParseState *state = NULL;
if (jb1 == NULL)
PG_RETURN_JSONB_P(jb1);
if (!JB_ROOT_IS_OBJECT(jb1))
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("Can only take objects")));
elog(NOTICE, "print_kv_pair(): ok0");
it1 = JsonbIteratorInit(&jb1->root);
r1 = JsonbIteratorNext(&it1, &v1, false);
if (r1 != WJB_BEGIN_OBJECT)
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("Iterator was not an object")));
elog(NOTICE, "print_kv_pair(): ok1");
//pushJsonbValue(&state, r1, NULL); //?? do wee need this?
//r1 = JsonbIteratorNext(&it1, &v1, false); //this seems unnecessary
JsonbValue *object = &v1;
elog(NOTICE, "print_kv_pair(): ok2");
Assert(object->type == jbvObject);
elog(NOTICE, "print_kv_pair(): ok3, nPairs = %d",
object->val.object.nPairs);
//iterating through key-value pairs
JsonbPair *ptr;
for (ptr = object->val.object.pairs;
ptr - object->val.object.pairs < object->val.object.nPairs;
ptr++)
{
//problem lines!!! //either elog crashes pg server
char *buf = pnstrdup(ptr->key.val.string.val,
ptr->key.val.string.len);
elog(NOTICE, "print_kv_pair(): k = %s",
(ptr->key).val.string.val); //debug
elog(NOTICE, "print_kv_pair(): v = %s",
DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(ptr->value.val.numeric))) ); //debug
}
elog(NOTICE, "print_kv_pair(): ok4");
PG_RETURN_JSONB_P(JsonbValueToJsonb(object));
}
On Mon, Mar 18, 2019 at 3:20 PM Andrew Gierth
wrote:
> >>>>> "T" == T L writes:
>
> T> //Problem line!!!
> T> //elog(NOTICE, "print_kv_pair(): k = %s, v = %s",
> T> ptr-> key.val.string.val, numeric_out(ptr->value.val.numeric));
>
> string.val isn't a C string (notice the "not null terminated" comment in
> the structure definition), and you can't call numeric_out like that.
> Either of those would crash it.
>
> You could use pnstrdup to get a valid C string, and use
> DatumGetCString(DirectFunctionCall1(
> numeric_out,
> NumericGetDatum(ptr->value.val.numeric)))
>
> to get the numeric value as a C string.
>
> --
> Andrew (irc:RhodiumToad)
>
Re: printing JsonbPair values of input JSONB on server side?
My fault on the first line.
You are right. The value type isn't actually numeric.
I changed the problem lines to:
//problem lines!!! //either elog crashes pg server
char *buf = pnstrdup(ptr->key.val.string.val,
ptr->key.val.string.len);
elog(NOTICE, "print_kv_pair(): k = %s", buf); //debug
if (ptr->value.type != jbvNumeric) {
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("value must be numeric")));
}
elog(NOTICE, "print_kv_pair(): v = %s",
DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(ptr->value.val.numeric))) ); //debug
Below is my test. It prints a strange character instead of "a"; and says
that the value isn't numeric.
I don't know why the value isn't numeric. I tried:
select print_kv_pair('{"a":1, "b": 2)');
and
select print_kv_pair('{"a":1.0, "b": 2.0}'::jsonb);
It seems there are other problems in my code, as if the memory `ptr` refers
to is invalid.
I suspect I didn't extract the JsonbPair correctly.
=> select print_kv_pair('{"a":1.0, "b": 2.0}'::jsonb);
NOTICE: print_kv_pair(): ok0
NOTICE: print_kv_pair(): ok1
NOTICE: print_kv_pair(): ok2
NOTICE: print_kv_pair(): ok3, nPairs = 2
NOTICE: print_kv_pair(): k = �J
ERROR: value must be numeric
On Mon, Mar 18, 2019 at 4:29 PM Andrew Gierth
wrote:
> >>>>> "T" == T L writes:
>
> T> //problem lines!!! //either elog crashes pg server
> T> char *buf = pnstrdup(ptr->key.val.string.val,
> T> ptr-> key.val.string.len);
> T> elog(NOTICE, "print_kv_pair(): k = %s",
> T> (ptr->key).val.string.val); //debug
>
> It doesn't help to make a null-terminated copy of the string if you're
> then just going to try and print the original.
>
> elog(NOTICE, "print_kv_pair(): k = %s", buf);
>
> T> elog(NOTICE, "print_kv_pair(): v = %s",
> T> DatumGetCString(DirectFunctionCall1(numeric_out,
> T> NumericGetDatum(ptr->value.val.numeric))) ); //debug
>
> That should work, _provided_ that value.type == jbvNumeric - did you
> consider checking that first?
>
> --
> Andrew (irc:RhodiumToad)
>
Re: printing JsonbPair values of input JSONB on server side?
I need this in my C code on the server side. Any link to the `jsonb_each` for this? Examples I found in a quick search are on the client side in SQL. I am just confused about the various jsonb types and how to effectively extract values and convert between them: There are Jsonb, JsonbValue (plus the associated JsonbPair ) to begin with. The ` JsonbToCStringWorker ` example that Andrew pointed out uses still another "JsonbContainer" type. But the type I get from "PG_GETARG_JSONB_P" is Jsonb. And it doesn't fit into " JsonbContainer" or the pointer math about "JsonPair" that I found online. What I am struggling with adapting some of the iterator code I saw is how to delete irrelevant code without breaking it. My use case is very restricted and handles hstore-like jsonb's. I don't need or want the code to have the ability to descend into nested objects or handle arrays etc., as they are invalid input in my case. I thought the pointer math example I found is easier to adapt, but I couldn't get a valid "JsonbPair" from the input parameter to feed into the pointer math. On Tue, Mar 19, 2019 at 9:50 AM Michel Pelletier wrote: > Yeah I'm not sure why you're looping using pointer math, the iterators are > there to provide that service. Another function to check out 'jsonb_each', > other than the set returning function parts, it does what it looks like > your are trying to do. > > -Michel > > On Mon, Mar 18, 2019 at 4:12 PM Andrew Gierth > wrote: > >> >>>>> "T" == T L writes: >> >> T> Below is my test. It prints a strange character instead of "a"; and >> T> says that the value isn't numeric. >> >> Yeah, there's plenty else wrong with your code. >> >> Did you look at how JsonbToCStringWorker does it? that looks like the >> best example I can find on a quick scan. >> >> -- >> Andrew (irc:RhodiumToad) >> >>
Re: printing JsonbPair values of input JSONB on server side?
Thanks a lot for the hint. I've used the iteration style and cleaned up the
code as far as I can.
It now correctly prints the keys and values, but the server crashes near
function return.
Any suggestions?
-- function code --
PG_FUNCTION_INFO_V1(print_kv_pair);
Datum
print_kv_pair(PG_FUNCTION_ARGS)
{
//1. extracting JsonbValue
Jsonb *jb = PG_GETARG_JSONB_P(0);
JsonbIterator *it;
JsonbValuev;
JsonbIteratorToken r;
JsonbParseState *state = NULL;
if (jb == NULL)
PG_RETURN_BOOL(false);
if (!JB_ROOT_IS_OBJECT(jb))
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("Can only take objects")));
it = JsonbIteratorInit(&jb->root);
r = JsonbIteratorNext(&it, &v, false);
if (r != WJB_BEGIN_OBJECT)
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("Iterator was not an object")));
//2. iterating through key-value pairs
char *buf;
while ((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE)
{
switch (r) {
case WJB_KEY:
buf = pnstrdup(v.val.string.val, v.val.string.len);
elog(NOTICE, "print_kv_pair(): k = %s", buf); //debug
break;
case WJB_VALUE:
if (v.type != jbvNumeric) {
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("value must be numeric")));
}
elog(NOTICE, "print_kv_pair(): v = %s",
DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(v.val.numeric))) ); //debug
break;
case WJB_END_OBJECT:
break;
default:
elog(ERROR, "invalid JsonbIteratorNext rc: %d", (int ) r);
}
}
elog(NOTICE, "print_kv_pair(): ok4");
PG_RETURN_BOOL(true);
}
-- output --
=> select print_kv_pair('{"a":1, "b": 2}');
NOTICE: print_kv_pair(): k = a
NOTICE: print_kv_pair(): v = 1
NOTICE: print_kv_pair(): k = b
NOTICE: print_kv_pair(): v = 2
NOTICE: print_kv_pair(): ok4
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
On Tue, Mar 19, 2019 at 2:22 PM Michel Pelletier
wrote:
> jsonb_each is a wrapper around each_worker_jsonb. It produces a row for
> every key/value pair in an object.
>
>
> https://doxygen.postgresql.org/jsonfuncs_8c.html#a7511a3aa3918eb956f3f4211d07bdbb0
>
> the iteration is:
>
> while ((r = JsonbIteratorNext(&it, &v, skipNested)) != WJB_DONE)
>
>
>
> On Tue, Mar 19, 2019 at 11:20 AM T L wrote:
>
>> I need this in my C code on the server side. Any link to the `jsonb_each`
>> for this? Examples I found in a quick search are on the client side in SQL.
>>
>> I am just confused about the various jsonb types and how to effectively
>> extract values and convert between them:
>>
>> There are Jsonb, JsonbValue (plus the associated JsonbPair ) to begin
>> with. The ` JsonbToCStringWorker ` example that Andrew pointed out uses
>> still another "JsonbContainer" type.
>> But the type I get from "PG_GETARG_JSONB_P" is Jsonb. And it doesn't fit
>> into " JsonbContainer" or the pointer math about "JsonPair" that I found
>> online.
>>
>> What I am struggling with adapting some of the iterator code I saw is how
>> to delete irrelevant code without breaking it. My use case is very
>> restricted and handles hstore-like jsonb's.
>> I don't need or want the code to have the ability to descend into nested
>> objects or handle arrays etc., as they are invalid input in my case.
>>
>> I thought the pointer math example I found is easier to adapt, but I
>> couldn't get a valid "JsonbPair" from the input parameter to feed into the
>> pointer math.
>>
>>
>>
>>
>>
>>
>> On Tue, Mar 19, 2019 at 9:50 AM Michel Pelletier <
>> pelletier.mic...@gmail.com> wrote:
>>
>>> Yeah I'm not sure why you're looping using pointer math, the iterators
>>> are there to provide that service. Another function to check out
>>> 'jsonb_each', other than the set returning function parts, it does what it
>>> looks like your are trying to do.
>>>
>>> -Michel
>>>
>>> On Mon, Mar 18, 2019 at 4:12 PM Andrew Gierth <
>>> and...@tao11.riddles.org.uk> wrote:
>>>
>>>> >>>>> "T" == T L writes:
>>>>
>>>> T> Below is my test. It prints a strange character instead of "a"; and
>>>> T> says that the value isn't numeric.
>>>>
>>>> Yeah, there's plenty else wrong with your code.
>>>>
>>>> Did you look at how JsonbToCStringWorker does it? that looks like the
>>>> best example I can find on a quick scan.
>>>>
>>>> --
>>>> Andrew (irc:RhodiumToad)
>>>>
>>>>
Re: printing JsonbPair values of input JSONB on server side?
Thanks a lot. Fixing `CREATE FUNCTION` statement fixed the crash issue.
On Tue, Mar 19, 2019 at 3:18 PM Michel Pelletier
wrote:
> For server crashes you can't beat gdb in my opinion. It's a challenge but
> worth it in the long run to have gdb skills if you're coding in C (or
> Python, since pdb shares many of gdb's keybindings).
>
> But just looking at the function I don't see what's immediately wrong,
> what's your CREATE FUNCTION statement look like?
>
> On Tue, Mar 19, 2019 at 1:08 PM T L wrote:
>
>> Thanks a lot for the hint. I've used the iteration style and cleaned up
>> the code as far as I can.
>> It now correctly prints the keys and values, but the server crashes near
>> function return.
>>
>> Any suggestions?
>>
>> -- function code --
>>
>> PG_FUNCTION_INFO_V1(print_kv_pair);
>> Datum
>> print_kv_pair(PG_FUNCTION_ARGS)
>> {
>> //1. extracting JsonbValue
>> Jsonb *jb = PG_GETARG_JSONB_P(0);
>> JsonbIterator *it;
>> JsonbValuev;
>> JsonbIteratorToken r;
>> JsonbParseState *state = NULL;
>>
>> if (jb == NULL)
>> PG_RETURN_BOOL(false);
>>
>> if (!JB_ROOT_IS_OBJECT(jb))
>> ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
>> errmsg("Can only take objects")));
>>
>> it = JsonbIteratorInit(&jb->root);
>> r = JsonbIteratorNext(&it, &v, false);
>> if (r != WJB_BEGIN_OBJECT)
>> ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
>> errmsg("Iterator was not an object")));
>>
>> //2. iterating through key-value pairs
>> char *buf;
>> while ((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE)
>> {
>> switch (r) {
>> case WJB_KEY:
>> buf = pnstrdup(v.val.string.val, v.val.string.len);
>> elog(NOTICE, "print_kv_pair(): k = %s", buf); //debug
>> break;
>> case WJB_VALUE:
>> if (v.type != jbvNumeric) {
>> ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
>> errmsg("value must be numeric")));
>> }
>> elog(NOTICE, "print_kv_pair(): v = %s",
>> DatumGetCString(DirectFunctionCall1(numeric_out,
>> NumericGetDatum(v.val.numeric))) ); //debug
>> break;
>> case WJB_END_OBJECT:
>> break;
>> default:
>> elog(ERROR, "invalid JsonbIteratorNext rc: %d", (int ) r);
>> }
>> }
>> elog(NOTICE, "print_kv_pair(): ok4");
>>
>> PG_RETURN_BOOL(true);
>> }
>>
>> -- output --
>>
>> => select print_kv_pair('{"a":1, "b": 2}');
>> NOTICE: print_kv_pair(): k = a
>> NOTICE: print_kv_pair(): v = 1
>> NOTICE: print_kv_pair(): k = b
>> NOTICE: print_kv_pair(): v = 2
>> NOTICE: print_kv_pair(): ok4
>> server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Failed.
>> !>
>>
>>
>> On Tue, Mar 19, 2019 at 2:22 PM Michel Pelletier <
>> pelletier.mic...@gmail.com> wrote:
>>
>>> jsonb_each is a wrapper around each_worker_jsonb. It produces a row for
>>> every key/value pair in an object.
>>>
>>>
>>> https://doxygen.postgresql.org/jsonfuncs_8c.html#a7511a3aa3918eb956f3f4211d07bdbb0
>>>
>>> the iteration is:
>>>
>>> while ((r = JsonbIteratorNext(&it, &v, skipNested)) != WJB_DONE)
>>>
>>>
>>>
>>> On Tue, Mar 19, 2019 at 11:20 AM T L wrote:
>>>
>>>> I need this in my C code on the server side. Any link to the
>>>> `jsonb_each` for this? Examples I found in a quick search are on the client
>>>> side in SQL.
>>>>
>>>> I am just confused about the various jsonb types and how to effectively
>>>> extract values and convert between them:
>>>>
>>>> There are Jsonb, JsonbValue (plus the associated JsonbPair ) to begin
>>>> with. The ` JsonbToCStringWorker ` example that Andrew pointed out uses
>>>> still another "JsonbContainer" type.
>>>> But the type I get from "PG_GETARG_JSONB_P" is Jsonb. And it doesn't
>>>>
Re: printing JsonbPair values of input JSONB on server side?
BTW, my pointer math code was trying to mimic the below code I found for
"uniqueifyJsonbObject".
I just removed the logic for dropping duplicates. My difficulty is that I
couldn't find out how to interface the jsonb object I get from
my "print_kv_pair()" to this function. Just out of curiosity, I am still
interested in finding a way to extract
and feed the JsonValue the right way.
static void
uniqueifyJsonbObject(JsonbValue *object)
{
boolhasNonUniq = false;
Assert(object->type == jbvObject);
if (object->val.object.nPairs > 1)
qsort_arg(object->val.object.pairs, object->val.object.nPairs,
sizeof(JsonbPair),
lengthCompareJsonbPair, &hasNonUniq);
if (hasNonUniq)
{
JsonbPair *ptr = object->val.object.pairs + 1,
*res = object->val.object.pairs;
while (ptr - object->val.object.pairs < object->val.object.nPairs)
{
// Avoid copying over duplicate
if (lengthCompareJsonbStringValue(ptr, res) != 0)
{
res++;
if (ptr != res)
memcpy(res, ptr, sizeof(JsonbPair));
}
ptr++;
}
object->val.object.nPairs = res + 1 - object->val.object.pairs;
}
}
On Tue, Mar 19, 2019 at 9:50 AM Michel Pelletier
wrote:
> Yeah I'm not sure why you're looping using pointer math, the iterators are
> there to provide that service. Another function to check out 'jsonb_each',
> other than the set returning function parts, it does what it looks like
> your are trying to do.
>
> -Michel
>
> On Mon, Mar 18, 2019 at 4:12 PM Andrew Gierth
> wrote:
>
>> >>>>> "T" == T L writes:
>>
>> T> Below is my test. It prints a strange character instead of "a"; and
>> T> says that the value isn't numeric.
>>
>> Yeah, there's plenty else wrong with your code.
>>
>> Did you look at how JsonbToCStringWorker does it? that looks like the
>> best example I can find on a quick scan.
>>
>> --
>> Andrew (irc:RhodiumToad)
>>
>>
