Get last key from jsonb (postgres)-CodePudding

Considering the following content on a jsonb field, and that the keys immediately after "audio" are random (I'll never know which value it'll be returning beforehand): is there any way I can query for the last key inside "audio" ("2814462280" in this example)?

   {
        "test": {
            "audio": {
                "1175828715": {
                    "direction": "outbound",
                },
                "2814462280": {
                    "direction": "inbound",
                }
            }
        }
    }

CodePudding user response：

Object.keys can be useful in this case. If you want to query for the last key. you can try following

const obj =    {
        "test": {
            "audio": {
                "1175828715": {
                    "direction": "outbound",
                },
                "2814462280": {
                    "direction": "inbound",
                }
            }
        }
    }

const audio_obj:any = obj.test.audio
const keys = Object.keys(audio_obj)
const len = keys.length
const lastKey: string = keys[len-1] // "2814462280"

if you want to get the last object you can add one line code

const lastObj = audio_obj[lastKey] // {"direction": "inbound"}

Hope this helps you.

CodePudding user response：

I've wrote a function to dynamically construct jsonb path to get the last key value.

As was previously mentioned in the comments section Postgres can reorder the keys in a jsonb field.

Here is the demo.

do $$
declare
  -- instantiate all the necessary variables
  audio_json jsonb;
  audio_json_keys text[];
  last_key_index integer;
  last_key_pointer text;
  result_json jsonb;
  query text;
  
begin 

audio_json := (select (metadata->>'test')::jsonb->>'audio' from test);
audio_json_keys := (select array_agg(keys) from jsonb_object_keys(audio_json::jsonb) keys);
last_key_index := (select array_length(audio_json_keys, 1));
last_key_pointer := audio_json_keys[last_key_index];

query := 'select (''' || audio_json::text || ''')::jsonb->>''' || last_key_pointer::text || '''';
execute query into result_json;

raise info '%', result_json;

end $$;