sql - 从 postgresql DB 中的 jsonb 列检索数据作为数组，返回空结果

Question

我在 postgresql 数据库中有表用户，该表包含一个类型为 jsonb 的列设置。这是 json 格式:

{
  "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": "any@gmail.com",
    "_password": "grmbn9",
    "_username": "username",
    "_member_id": 57076
  },
  "email_status": 2,
  "email_verify_code": 9579
}

而且我必须编写一个 postgres 函数来返回数组中的所有设备 FCM。这是我的职责。

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;

当我执行

SELECT GetUserFCM(33) as result;

它什么都不返回。任何帮助请问我如何从 json 对象中检索设备 FCM。还有其他更好的方法来检索 FCM 吗？

Answer 1

我会选择它:

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": "any@gmail.com",
    "_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)