A strange problem with PostgreSQL 15.4 jsonb_to_recordset and jsonb_populate_recordset

VitalyUshakov
September 25, 2023
296 views
1 vote
2 Answers

I have a postgresql function that takes a number of parameters, the last of which is jsonb.
It’s called with the following value of parameters (double quotes due to postgres diagnostic output):

SELECT vspm.rfd_add_f
(
  $1::bigint
  , $2::bigint
  , $3::bigint
  , $4::timestamptz
  , $5::timestamptz
  , $6::bigint
  , $7::bigint
  , $8::text
  , $9::text
  , $10::timestamptz
  , $11::text
  , $12::jsonb
)
","parameters: $1 = NULL, $2 = '1', $3 = '18014398509482060', $4 = NULL, $5 = NULL, $6 = '18014398509484942', $7 = '18014398509482082', $8 = NULL, $9 = NULL, $10 = NULL, $11 = NULL, $12 = '[{""id"":null,""modUserId"":null,""validFrom"":null,""validTo"":null,""clientId"":null,""client"":null,""rfdId"":null,""rfd"":null,""rfdNumber"":null,""rfdDate"":null,""managerId"":null,""manager"":null,""estimateId"":null,""estimate"":null,""productionNeedId"":""18014398509489518"",""quantity"":""111000000"",""sortOrder"":null,""wantedDeliveryDate"":""2023-12-11T18:00:00Z"",""objectId"":null,""object"":null,""section"":null,""nomenclatureId"":null,""nomenclature"":null,""muId"":null,""mu"":null,""remarks"":null},{""id"":null,""modUserId"":null,""validFrom"":null,""validTo"":null,""clientId"":null,""client"":null,""rfdId"":null,""rfd"":null,""rfdNumber"":null,""rfdDate"":null,""managerId"":null,""manager"":null,""estimateId"":null,""estimate"":null,""productionNeedId"":""18014398509489524"",""quantity"":""222000000"",""sortOrder"":null,""wantedDeliveryDate"":""2023-12-12T18:00:00Z"",""objectId"":null,""object"":null,""section"":null,""nomenclatureId"":null,""nomenclature"":null,""muId"":null,""mu"":null,""remarks"":null},{""id"":null,""modUserId"":null,""validFrom"":null,""validTo"":null,""clientId"":null,""client"":null,""rfdId"":null,""rfd"":null,""rfdNumber"":null,""rfdDate"":null,""managerId"":null,""manager"":null,""estimateId"":null,""estimate"":null,""productionNeedId"":""18014398509489530"",""quantity"":""333000000"",""sortOrder"":null,""wantedDeliveryDate"":""2023-12-13T18:00:00Z"",""objectId"":null,""object"":null,""section"":null,""nomenclatureId"":null,""nomenclature"":null,""muId"":null,""mu"":null,""remarks"":null}]'",,,,,,,,"PostgreSQL JDBC Driver","client backend",,0

The following fields are significant in this case:

[
    {
        "productionNeedId": "18014398509489518"
        , "quantity": "111000000"
        , "wantedDeliveryDate": "2023-12-11T21:00:00+03:00"
    }
    , {
        "productionNeedId": "18014398509489524"
        , "quantity": "222000000"
        , "wantedDeliveryDate": "2023-12-12T21:00:00+03:00"
    }
    , {
        "productionNeedId": "18014398509489530"
        , "quantity": "333000000"
        , "wantedDeliveryDate": "2023-12-13T21:00:00+03:00"
    }
]

At the beginning of the function for debugging there are the following statements:

RAISE LOG '==> rfditem_add_many_f: json quantity = %, productionNeedId = %, wantedDeliveryDate = %'
  , (pitems::jsonb->0)::jsonb->>'quantity'
  , (pitems::jsonb->0)::jsonb->>'productionNeedId'
  , (pitems::jsonb->0)::jsonb->>'wantedDeliveryDate'
;
RAISE LOG '==> rfditem_add_many_f: json quantity = %, productionNeedId = %, wantedDeliveryDate = %'''
  , (pitems::jsonb->1)::jsonb->>'quantity'
  , (pitems::jsonb->1)::jsonb->>'productionNeedId'
  , (pitems::jsonb->1)::jsonb->>'wantedDeliveryDate'
;
RAISE LOG '==> rfditem_add_many_f: json quantity = %, productionNeedId = %, wantedDeliveryDate = %'''
  , (pitems::jsonb->2)::jsonb->>'quantity'  
  , (pitems::jsonb->2)::jsonb->>'productionNeedId'
  , (pitems::jsonb->2)::jsonb->>'wantedDeliveryDate'
;

And I get the correct output:

"==> rfditem_add_many_f: json quantity = 111000000, productionNeedId = 18014398509489518, wantedDeliveryDate = 2023-12-11T18:00:00Z",,,,,"PL/pgSQL function rfditem_add_many_f(bigint,bigint,timestamp with time zone,timestamp with time zone,bigint,timestamp with time zone,text,jsonb) line 20 at RAISE


"==> rfditem_add_many_f: json quantity = 222000000, productionNeedId = 18014398509489524, wantedDeliveryDate = 2023-12-12T18:00:00Z'",,,,,"PL/pgSQL function rfditem_add_many_f(bigint,bigint,timestamp with time zone,timestamp with time zone,bigint,timestamp with time zone,text,jsonb) line 25 at RAISE


"==> rfditem_add_many_f: json quantity = 333000000, productionNeedId = 18014398509489530, wantedDeliveryDate = 2023-12-13T18:00:00Z'",,,,,"PL/pgSQL function rfditem_add_many_f(bigint,bigint,timestamp with time zone,timestamp with time zone,bigint,timestamp with time zone,text,jsonb) line 30 at RAISE

But when I further try to process this jsonb with the jsonb_to_recordset function, the values of fields productionNeedId and wantedDeliveryDate are not found.

FOR lrecord IN
  SELECT
    id, moduserId, validFrom, validTo, clientId, rfdId, productionNeedId, quantity, sortOrder, wantedDeliveryDate, remarks
  FROM jsonb_to_recordset(pitems) AS (id bigint, moduserId bigint, validFrom timestamptz, validTo timestamptz, clientId bigint, rfdId bigint, productionNeedId bigint, quantity bigint, sortOrder int, wantedDeliveryDate timestamptz, remarks text)
LOOP
      RAISE LOG '==> rfditem_add_many_f read : validFrom = %, validTo = %, clientid = %, rfdId = %, productionNeedId = %, quantity = %, sortOrder = %, wantedDeliveryDate = %, remarks = %'
        , coalesce(lrecord.validFrom, '1900-01-01'::timestamptz)
        , coalesce(lrecord.validTo, '1900-01-01'::timestamptz)
        , coalesce(lrecord.clientId, -1)
        , coalesce(lrecord.rfdId, -1)
        , coalesce(lrecord.productionNeedId, -1)
        , coalesce(lrecord.quantity, -1)
        , coalesce(lrecord.sortOrder, -1)
        , coalesce(lrecord.wantedDeliveryDate, '1900-01-01'::timestamptz)
        , coalesce(lrecord.remarks, '')
      ;

"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = -1, productionNeedId = -1, quantity = 111000000, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "

The remaining two elements of the json array are not processed because later in the function, when trying to add a record to the database table, an integrity constraint violation occurs.

I also tried using the following function calls:

FOR lrecord IN
  SELECT
    validFrom, validTo, clientId, rfdId, productionNeedId, quantity, sortOrder, wantedDeliveryDate, remarks
  FROM jsonb_populate_recordset(null::rfditem_type, pitems)
LOOP

with rfditem_type declared as:

CREATE TYPE rfditem_type AS
(
  id                    bigint

  , moduserId           bigint
  , validFrom           timestamptz
  , validTo             timestamptz

  , clientId            bigint

  , rfdId               bigint
  , productionNeedId    bigint
  , quantity            bigint

  , sortOrder           integer

  , wantedDeliveryDate  timestamptz

  , remarks             text
);

But the result was even worse (the debugging output was the same):

"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = 111000000, productionNeedId = -1, quantity = -1, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "

...
"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = 222000000, productionNeedId = -1, quantity = -1, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "
...
"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = 333000000, productionNeedId = -1, quantity = -1, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "

the quantity’s value was assigned to the rfdId field, the others fields were unassigned

and

FOR lrecord IN
  SELECT * from jsonb_populate_recordset(null::rfditem_type, pitems)
LOOP

In this case the result was slightly different:

"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = -1, productionNeedId = -1, quantity = 111000000, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "

that is, the value for the quantity field was assigned correctly, but the fields productionNeedId and wantedDeliveryDate were not found

I had an idea that this might be due to different case of letters in the field names, but then why does everything work correctly in the diagnostic output? (I would like to be sure that this is the case before editing the backend).

Answers

Chosen as BEST ANSWER
- VitalyUshakov
- September 25, 2023 at 4:56 am
- 0 votes
0
Yes, the problem was definitely in the case of the json properties names. After the background was redesigned and began to transfer these names only in lower case, everything worked.

I would like to wish the Postgresql team greater consistency in functions and operators for working with json. If the properties in the first diagnostic output ((pitems::jsonb->0)::jsonb->>'productionNeedId') were not located in the same way as subsequently in the functions jsonb_to_recordset and jsonb_populate_recordset, the problem would have been identified and solved much earlier

(Edit)

Yes, this is almost certainly due to the different casing. When you do not quote your identifiers, Postgres treats them as if they were lowercase, which will cause them not to be found in the JSON object. Using ->> with the property name as a string literal does not have this problem.

So use

FOR lrecord IN
  SELECT *
  FROM jsonb_to_recordset(pitems) AS ("id" bigint, "moduserId" bigint, "validFrom" timestamptz, "validTo" timestamptz, "clientId" bigint, "rfdId" bigint, "productionNeedId" bigint, "quantity" bigint, "sortOrder" int, "wantedDeliveryDate" timestamptz, "remarks" text)
LOOP
  RAISE LOG '==> rfditem_add_many_f read : validFrom = %, validTo = %, clientid = %, rfdId = %, productionNeedId = %, quantity = %, sortOrder = %, wantedDeliveryDate = %, remarks = %'
    , coalesce(lrecord."validFrom", '1900-01-01'::timestamptz)
    , coalesce(lrecord."validTo", '1900-01-01'::timestamptz)
    , coalesce(lrecord."clientId", -1)
    , coalesce(lrecord."rfdId", -1)
    , coalesce(lrecord."productionNeedId", -1)
    , coalesce(lrecord."quantity", -1)
    , coalesce(lrecord."sortOrder", -1)
    , coalesce(lrecord."wantedDeliveryDate", '1900-01-01'::timestamptz)
    , coalesce(lrecord."remarks", '')
  ;

Please signup or login to give your own answer.

Click here to cancel reply.