ORDER BY specific field in Nested JSON list-CodePudding

How to order by field 'created_at' inside list. Sample JSON attached below.

create table the_table (id int, value jsonb);
insert into the_table
values 
(1, 
'{
  "created_on": 123,
  "approvals": [
    {
      "id": "446",
      "isPending": true,
      "isReadyToApprove": true,
      "created_at": 124
    },
    {
      "id": "191",
      "isPending": true,
      "isReadyToApprove": true,
      "created_at": 124
    }
  ]
}'), 
(2, 
'{
  "created_on": 456,
  "approvals": [
    {
      "id": "447",
      "isPending": true,
      "isReadyToApprove": true,
      "created_at": 125
    },
    {
      "id": "192",
      "isPending": true,
      "isReadyToApprove": true,
      "created_at": 125
    }
  ]
}'), 
(3, 
'{
  "created_on": 789,
  "approvals": [
    {
      "id": "448",
      "isPending": true,
      "isReadyToApprove": true,
      "created_at": 126
    },
    {
      "id": "193",
      "isPending": true,
      "isReadyToApprove": true,
      "created_at": 126
    }
  ]
}');

CodePudding user response：

If you don't care which of the many created_at you are sorting by (which sounds a bit strange), then just pick the first one:

select *
from the_table
order by (value -> 'approvals' -> 0 ->> 'created_at')::int