Select only the jsonb object keys which are filtered and ordered by values in child object-CodePudding

in postgres 13, I have a Jsonb object and I am able to get only the keys using jsonb_object_keys like this.

SELECT keys from jsonb_object_keys('{
    "135": {
      "timestamp": 1659010504308,
      "priority": 5,
      "age": 20
    },
    "136": {
      "timestamp": 1659010504310,
      "priority": 2,
      "age": 20
    },
    "137": {
      "timestamp": 1659010504312,
      "priority": 2,
      "age": 20
    },
    "138": {
      "timestamp": 1659010504319,
      "priority": 1,
      "age": 20
    }}') as keys

Now, I want to get the keys which have priority more than 1 and which are ordered by priority and timestamp

I am able to achieve this using this query

select key from (
SELECT data->>'key' key, data->'value' value
FROM  
   jsonb_path_query(
   '{
  "135": {
    "name": "test1",
    "timestamp": 1659010504308,
    "priority": 5,
    "age": 20
    },
   "136": {
    "name": "test2",
    "timestamp": 1659010504310,
    "priority": 7,
    "age": 20
    },
   "137": {
    "name": "test3",
    "timestamp": 1659010504312,
    "priority": 5,
    "age": 20
    },
    "138": {
     "name": "test4",
     "timestamp": 1659010504319,
     "priority": 1,
     "age": 20
    }}'
   , '$.keyvalue() ? (@.value.priority > 1)')
    as data) as foo, jsonb_to_record(value) x("name" text, "timestamp" decimal,
                                                          "priority" int, 
                                                          "age" int)
    order by priority desc, timestamp desc

This doesn't seem to be the efficient way of doing this.

Please share if this can be achieved in a better way (by using jsonb_object_keys !??)

Thanks in advance.

CodePudding user response：

I would first 'normalize' JSON data into a table (the t CTE) and then do a trivial select.

with t (key, priority, ts) as
(
 select key, (value ->> 'priority')::integer, value ->> 'timestamp' 
 from jsonb_each('{
    "135": {"timestamp": 1659010504308,"priority": 5,"age": 20},
    "136": {"timestamp": 1659010504310,"priority": 2,"age": 20},
    "137": {"timestamp": 1659010504312,"priority": 2,"age": 20},
    "138": {"timestamp": 1659010504319,"priority": 1,"age": 20}
 }')
)
select key 
from t 
where priority > 1 
order by priority, ts;