Thread: printing JsonbPair values of input JSONB on server side?

printing JsonbPair values of input JSONB on server side?

From
T L
Date:
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;
        JsonbValue    v1;
        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?

From
Andrew Gierth
Date:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
T L
Date:

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;
    JsonbValue    v1;
    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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
Andrew Gierth
Date:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
T L
Date:
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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
Andrew Gierth
Date:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
Michel Pelletier
Date:
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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
T L
Date:
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.michel@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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
Michel Pelletier
Date:
jsonb_each is a wrapper around each_worker_jsonb.  It produces a row for every key/value pair in an object.


the iteration is:

  while ((r = JsonbIteratorNext(&it, &v, skipNested)) != WJB_DONE)



On Tue, Mar 19, 2019 at 11:20 AM T L <tinlyx@gmail.com> 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.michel@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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
T L
Date:
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;
    JsonbValue    v;
    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.michel@gmail.com> wrote:
jsonb_each is a wrapper around each_worker_jsonb.  It produces a row for every key/value pair in an object.


the iteration is:

  while ((r = JsonbIteratorNext(&it, &v, skipNested)) != WJB_DONE)



On Tue, Mar 19, 2019 at 11:20 AM T L <tinlyx@gmail.com> 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.michel@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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
Michel Pelletier
Date:
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 <tinlyx@gmail.com> 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;
    JsonbValue    v;
    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.michel@gmail.com> wrote:
jsonb_each is a wrapper around each_worker_jsonb.  It produces a row for every key/value pair in an object.


the iteration is:

  while ((r = JsonbIteratorNext(&it, &v, skipNested)) != WJB_DONE)



On Tue, Mar 19, 2019 at 11:20 AM T L <tinlyx@gmail.com> 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.michel@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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
T L
Date:
Thanks a lot. Fixing `CREATE FUNCTION` statement fixed the crash issue.


On Tue, Mar 19, 2019 at 3:18 PM Michel Pelletier <pelletier.michel@gmail.com> 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 <tinlyx@gmail.com> 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;
    JsonbValue    v;
    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.michel@gmail.com> wrote:
jsonb_each is a wrapper around each_worker_jsonb.  It produces a row for every key/value pair in an object.


the iteration is:

  while ((r = JsonbIteratorNext(&it, &v, skipNested)) != WJB_DONE)



On Tue, Mar 19, 2019 at 11:20 AM T L <tinlyx@gmail.com> 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.michel@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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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?

From
T L
Date:
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)
{
    bool        hasNonUniq = 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 <pelletier.michel@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 <andrew@tao11.riddles.org.uk> wrote:
>>>>> "T" == T L <tinlyx@gmail.com> 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)