时间戳

首页 > 解决方案 > 在 11.7 上解压缩 JSONB 数据并将其用作 PL/PgSQL 函数

postgresql - 在 11.7 上解压缩 JSONB 数据并将其用作 PL/PgSQL 函数

问题描述

我正在开发一个函数，该函数采用一对 JSON 数组和一些设置值，然后在数组中的项目之间进行比较。在此之前，我试图在 PG/PlSQL 中解析 JSONB。这个在函数体中带有硬编码 JSON 的版本可以工作：

CREATE OR REPLACE FUNCTION tools.try_unpacking_hard_coded (
     base_jsonb_in                        jsonb)

RETURNS TABLE (
    base_text                             citext,
    base_id                               citext)
AS $BODY$

BEGIN

RETURN QUERY

with
base_expanded AS (
 select *
   from json_populate_recordset(
        null::record,
          '[
             {"base_text":"Red Large Special","base_id":1},
             {"base_text":"Blue Small","base_id":5},
             {"base_text":"Green Medium Special","base_id":87}
           ]')
      AS unpacked (base_text citext, base_id citext)
 )

select base_expanded.base_text,
       base_expanded.base_id

  from base_expanded;

END
$BODY$
LANGUAGE plpgsql;

这是一个示例调用和输出：

select * from try_unpacking_hard_coded('{}'::jsonb);

base_text           base_id
Red Large Special        1
Blue Small               5
Green Medium Special    87

但是，当我尝试传入 JSON 文本并将其解析为参数时，我只得到NULL结果。我得到了正确的名称和列数，以及正确的行数……但没有值。


CREATE OR REPLACE FUNCTION tools.try_unpacking (
     base_jsonb_in   jsonb)

RETURNS TABLE (
    base_text         citext,
    base_id           citext)

AS $BODY$

BEGIN

RETURN QUERY

WITH
base_expanded AS (

 select *
   from jsonb_populate_recordset(
          null::record,
          base_jsonb_in)
      AS base_unpacked (base_text citext, base_id citext)
 )

select base_expanded.base_text,
       base_expanded.base_id

  from base_expanded;

END 
$BODY$
LANGUAGE plpgsql;

这是电话：

select *  from try_unpacking (
'[{"text":"Red Large Special","id":"1"},
{"text":"Blue Small","id":"5"},
{"text":"Green Medium Special","id":"87"}]'
);

结果：

base_text   base_id
NULL        NULL
NULL        NULL
NULL        NULL

有人可以指出我正确的方向来解决这个问题吗？

跟进

Barbaros Özhan 好心指出我的错误，这是一个工作版本：

CREATE OR REPLACE FUNCTION tools.try_unpacking (
     base_jsonb_in   jsonb)

RETURNS TABLE (
    base_text         citext,
    base_id           citext)

AS $BODY$

BEGIN

RETURN QUERY

WITH
base_expanded AS (

 select *
   from jsonb_populate_recordset(
          null::record,
          base_jsonb_in)
      AS base_unpacked (text citext, id citext)
 )

select base_expanded.text AS base_text,
       base_expanded.id AS base_id

  from base_expanded;

END 
$BODY$
LANGUAGE plpgsql;

顺便说一句，重命名的原因是我接下来将有第二个带comparison值的参数。

标签： postgresqlparsingplpgsqljsonb

解决方案

作为函数参数的 JSON 值的键值应符合从jsonb_populate_recordset()函数返回的各个列的定义的单独别名：

CREATE OR REPLACE FUNCTION try_unpacking (
     base_jsonb_in   jsonb)

RETURNS TABLE (
    base_text        varchar(100),
    base_id          varchar(100))

AS $BODY$

BEGIN

RETURN QUERY

WITH
base_expanded AS (

 select *
   from jsonb_populate_recordset(
          null::record,
          base_jsonb_in)
      AS base_unpacked (bt varchar(100), bi varchar(100))
 )
select b.bt, b.bi
  from base_expanded b;

END 
$BODY$
LANGUAGE plpgsql;

select *  from try_unpacking (
'[{"bt":"Red Large Special","bi":"1"},
{"bt":"Blue Small","bi":"5"},
{"bt":"Green Medium Special","bi":"87"}]'
);

base_text               base_id
Red Large Special       1
Blue Small              5
Green Medium Special    87

请注意，返回记录的列标题仍保留base_text为base_id 

RETURNS TABLE (
    base_text        varchar(100),
    base_id          varchar(100)
)

部分。

Demo

推荐阅读