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 ?

what's your postgres version?..

Vao Tsun
– Vao Tsun

2017-10-14 17:35:26 +00:00
Commented Oct 14, 2017 at 17:35 — Vao Tsun
– Vao Tsun, Commented Oct 14, 2017 at 17:35
@VaoTsun I am using postgresql-9.4.

Amjad Omari
– Amjad Omari

2017-10-14 17:49:16 +00:00
Commented Oct 14, 2017 at 17:49 — Amjad Omari
– Amjad Omari, Commented Oct 14, 2017 at 17:49

Vao Tsun · Accepted Answer · 2017-10-14 17:39:50Z

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)

answered Oct 14, 2017 at 17:39

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amjad Omari Over a year ago

And how could I insert this in a postgresql function to return the result in Array? I'm new in postgres.

Vao Tsun Over a year ago

the select in answer returns Array

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related