Unpacking and using JSONB data as a PL/PgSQL function on 11.7

Question

I'm working towards a function that takes a pair of JSON arrays and some setting values, and then makes comparisons between the items in the arrays. Leading up to that, I'm trying to get the JSONB parsed within PG/PlSQL. This version with hard-coded JSON in the function body works:

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;

Here's a sample call and output:

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

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

However, when I try passing in the JSON text and parsing it as a parameter, I get back nothing but NULL results. I get the correct name and number of columns, and the correct number of rows...but no values.


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;

Here's the call:

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

And the result:

base_text   base_id
NULL        NULL
NULL        NULL
NULL        NULL

Can someone point me in the right direction to fix this?

Follow-up

Barbaros Özhan kindly pointed out my mistake, here's a working version:

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;

BTW, the reason for the renaming is that I'll next have a second parameter with comparison values.

just need to fix the key names in the second case such as `base_text` instead of `text`, and `base_id` instead of `id`. — Barbaros Özhan, May 09 '20 at 10:47
Perfect! If you post your comment as an answer, then I can accept it. — Morris de Oryx, May 09 '20 at 21:45

score 1 · Accepted Answer · answered May 09 '20 at 22:09

Key values for the JSON value as argument of the function should conform to the defined individual aliases for respective returned columns from jsonb_populate_recordset() function :

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

Notice that the column titles for the returned records are still kept as base_text and base_id of those are in the

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

section.

Demo

Unpacking and using JSONB data as a PL/PgSQL function on 11.7

Follow-up

1 Answers1