retrieving data from jsonb column in postgresql DB functions as an array, return empty result

Question

I have table users in a postgresql DB, The table contains a column settings of type jsonb. and here it is the json format:

{
  "device": {
    "352fef5aa349d63c": {
      "fcm": "Rg_4rdTaPwifTh-sP8gtRdI7VdMO_sShhuYbEpplVtmSfmIo8kkmqzIaFxfw59QXg3il95Y",
      "agent": "android",
      "language": "en",
      "app_version": 1
    },
    "3a922f2ead22ecb6": {
      "fcm": "MkqSrdTkPwiU32-sPKA_S8I7VdMO_tShhuYbEpplVtmSfmLo6kkmqzIaFxfw59QXg3il94X",
      "agent": "android",
      "language": "en",
      "app_version": 6
    }
  },
  "data": {
    "_email": "[email protected]",
    "_password": "grmbn9",
    "_username": "username",
    "_member_id": 57076
  },
  "email_status": 2,
  "email_verify_code": 9579
}

and I have to write a postgres function to return all devices FCMs in array. and here it is my function.

CREATE OR REPLACE FUNCTION GetUserFCM(userId int)
RETURNS TEXT[] 
AS $$
    DECLARE user_devices jsonb;
    DECLARE result TEXT[];
    DECLARE fcm TEXT[];
    DECLARE tmp TEXT;
BEGIN
    SELECT setting->'device' into user_devices FROM public."user" WHERE id = userId;
    SELECT ARRAY(SELECT jsonb_object_keys((SELECT setting->'device' FROM public."user" WHERE id = userId)::jsonb)) into result;

    FOR i IN 1 .. array_upper(result, 1)
    LOOP
        tmp := user_devices->i->'fcm';
        IF tmp IS NULL THEN
            PERFORM array_append(fcm, tmp);
        END IF;
    END LOOP;

    RETURN fcm;
END
$$
LANGUAGE plpgsql;

and when I execute

SELECT GetUserFCM(33) as result;

it returns nothing. any help how could I retrieve the devices FCMs from the json object please. and is there any other way better to retrieve the FCMs ?

Answer 1

I would just select it:

t=> with j(b) as (values('{
  "device": {
    "352fef5aa349d63c": {
      "fcm": "Rg_4rdTaPwifTh-sP8gtRdI7VdMO_sShhuYbEpplVtmSfmIo8kkmqzIaFxfw59QXg3il95Y",
      "agent": "android",
      "language": "en",
      "app_version": 1
    },
    "3a922f2ead22ecb6": {
      "fcm": "MkqSrdTkPwiU32-sPKA_S8I7VdMO_tShhuYbEpplVtmSfmLo6kkmqzIaFxfw59QXg3il94X",
      "agent": "android",
      "language": "en",
      "app_version": 6
    }
  },
  "data": {
    "_email": "[email protected]",
    "_password": "grmbn9",
    "_username": "username",
    "_member_id": 57076
  },
  "email_status": 2,
  "email_verify_code": 9579
}'::jsonb)
)
, parse as (select b->'device'->jsonb_object_keys(b->'device')->>'fcm' jb from j)
select array_agg(jb) from parse;
                                                                     array_agg
---------------------------------------------------------------------------------------------------------------------------------------------------
 {Rg_4rdTaPwifTh-sP8gtRdI7VdMO_sShhuYbEpplVtmSfmIo8kkmqzIaFxfw59QXg3il95Y,MkqSrdTkPwiU32-sPKA_S8I7VdMO_tShhuYbEpplVtmSfmLo6kkmqzIaFxfw59QXg3il94X}
(1 row)