如何在PostgreSQL中检索输入JSONB对象的JsonbPair值？-解网

问：

我正在尝试编写一个 PostgreSQL （11.2）服务器端函数来读取输入 JSONB 对象的键值对。我通过尝试这样做（在下面）来做到这一点print_kv_pair

从输入对象中提取 s 并JsonPairjsonb
循环访问键和值并打印它们。

例如，对于，我希望它打印'{"a":1, "b": 2}'

k = "a", v = 1
k = "b", v = 2

但是，代码为键输出奇怪的字符，并且值（和）不是我所期望的类型。请参阅问题末尾的示例输出。12numeric

有人可以解释如何修复代码并正确遍历键值对吗？

PG_FUNCTION_INFO_V1(print_kv_pair);
Datum
print_kv_pair(PG_FUNCTION_ARGS)
{
    //1. extracting JsonbValue
    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")));

    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")));

    JsonbValue *object = &v1;
    Assert(object->type == jbvObject);

    //2. 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!!!
        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
    }

    elog(NOTICE, "print_kv_pair(): ok4");

    PG_RETURN_BOOL(true);
}

禁用问题行的示例输出：

=> select print_kv_pair('{"a":1.0, "b": 2.0}'::jsonb);
NOTICE:  print_kv_pair(): k = $�K
ERROR:  value must be numeric

部件似乎无法正常工作，提取的值指向无效内存。1. extracting JsonbVaule

（我对JSONB或服务器端PostgreSQL编程不是很熟悉。任何建议都是值得赞赏的。

C json PostgreSQL 服务器端 jsonb

/*
/home/jian/Desktop/regress_pgsql/print_jsonb_key_value_pair.sql

https://stackoverflow.com/questions/55214579/how-to-retrieve-jsonbpair-values-of-input-jsonb-object-in-postgresql

gcc -I/home/jian/postgres/2023_05_25_beta5421/include/server -fPIC -c /home/jian/Desktop/regress_pgsql/print_jsonb_key_value_pair.c
gcc -shared  -o /home/jian/Desktop/regress_pgsql/print_jsonb_key_value_pair.so /home/jian/Desktop/regress_pgsql/print_jsonb_key_value_pair.o
 
*/
#include "postgres.h"

#include "utils/builtins.h"
#include "utils/array.h"
#include "funcapi.h"
#include "utils/lsyscache.h"
#include "utils/fmgrprotos.h"
#include "utils/jsonb.h"
#include "utils/numeric.h"

PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(print_jsonb_key_value_pair);

Datum 
print_jsonb_key_value_pair(PG_FUNCTION_ARGS)
{
    Jsonb *jb1 = PG_GETARG_JSONB_P(0);   
    JsonbIteratorToken r1;
    JsonbValue  v1;
    JsonbIterator *it1;

    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")));

    it1 = JsonbIteratorInit(&jb1->root);
    r1 = JsonbIteratorNext(&it1, &v1, true);

    if (r1 != WJB_BEGIN_OBJECT)
        ereport(ERROR,
                    (errcode(ERRCODE_INVALID_PARAMETER_VALUE)
                    ,errmsg("Iterator was not an object")));

    while ((r1 = JsonbIteratorNext(&it1, &v1, true)) != WJB_DONE)
    {
        if (r1 != WJB_KEY)
            continue;
        Assert(v1.type == jbvString);

        elog(NOTICE,"key: %s",(v1.val.string.val));
        if ((r1 = JsonbIteratorNext(&it1, &v1, true)) != WJB_VALUE)
            elog(ERROR, "unexpected jsonb token: %d", r1);
    
        if (v1.type != jbvNumeric)
            elog(ERROR, "value should be numeric data type");
        // Assert(v1.type == jbvNumeric);
        
        char    *values;
        Datum temp  = (Datum) (v1.val.numeric);

        values  = DatumGetCString(DirectFunctionCall1(numeric_out, temp));

        elog(NOTICE,"numeric values: %s", values);
    }
    PG_RETURN_BOOL(true);
}

测试用例：

select print_jsonb_key_value_pair('{"hello":-1.0000000000000001, "world": 2.011111101}'::jsonb);
select print_jsonb_key_value_pair('{"hello":1e6}'::jsonb);
select print_jsonb_key_value_pair('{"hello":1e-6}'::jsonb);
select print_jsonb_key_value_pair('{"hello":"will_fail_since_value_not_numeric"}'::jsonb);

上一个：在 AngularJS 1.4 中使用服务器端 JSON 数组对象作为登录详细信息

下一个：将 JSON 对象传递到服务器端并解析函数

如何在PostgreSQL中检索输入JSONB对象的JsonbPair值？

How to retrieve JsonbPair values of input JSONB object in PostgreSQL?

评论