Reports

Blacklisted phrase (0.5): Thank you
Long answer (-1):
Has code block (-0.5):
User mentioned (1): @AdrianKlaver
Self-answer (0.5):
Low reputation (0.5):

Thank you @AdrianKlaver for pointing me to JSON path queries. I have come up with the following alternative. It takes about the same amount of time as my previous solution and has a similar explain plan.

select
    (jsonb_path_query(data, '$.Payments[*]')->>'Id')::integer id,
    (jsonb_path_query(data, '$.Payments[*]')->>'TotalAmt')::decimal total_amt,
    cast(jsonb_path_query(data, '$.Payments[*]')->>'CreateTime' as timestamp with time zone) create_time,
    jsonb_path_query(data, '$.Payments[*]')->>'InvoiceRef' invoice_ref,
    jsonb_path_query(data, '$.Payments[*]')->>'CustomerRef' customer_ref,
    (jsonb_path_query(data, '$.Payments[*]')->'CustomFields'->0->>'Value')::integer order_number
from json_imports
where id = 7

Here is the explain plan

[
  {
    "Plan": {
      "Node Type": "Result",
      "Parallel Aware": false,
      "Async Capable": false,
      "Plans": [
        {
          "Node Type": "ProjectSet",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Plans": [
            {
              "Node Type": "Index Scan",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Scan Direction": "Forward",
              "Index Name": "json_imports_pkey",
              "Relation Name": "json_imports",
              "Alias": "json_imports",
              "Index Cond": "(id = 7)"
            }
          ]
        }
      ]
    }
  }
]

79474262