How do I select a JSONB field into a declared variable. I'm getting a null value-CodePudding

When I use a select into I'm getting a null value

SELECT ((payload->>'99')::jsonb)->>'BATCH_ID' into in_batch_id 
  from history where id = table.ID;

When I check the value of in_batch_id it is null. I need the value of the jsonb field put into the in_batch_id varible. I'm using postgresql.

{ "1": { "seq": null, "doc_id": null, "batch_id": "91" }, "2": { "seq": null, "doc_id": null, "batch_id": "91"

}, "99": { "seq": null, "doc_id": null, "emp_id": null, "batch_id": 91 } }

CodePudding user response：

((payload->>'99')::jsonb)->>'BATCH_ID' looks wrong.

You most likely want payload->'99'->>'BATCH_ID' or payload->99->>'BATCH_ID' instead. The property value at key 99 hardly is a string with JSON syntax.

CodePudding user response：

Why the casting between between jsonb and text? You should extract the value of key 99 as jsonb using the -> operator, not ->>

And according to your comment (which should have been an edit to your question) you need to use batch_id not BATCH_ID

So either:

in_batch_id := payload -> '99' ->> 'batch_id';

in_batch_id := payload #>> '{99,batch_id}';

Assuming that the variable in_batch_id is defined as text