• Pgsql数据库jsonb操作函数集合


    CREATE OR REPLACE FUNCTION "json_object_del_path"(
      "json"          json,
      "key_path"      TEXT[]
    )
      RETURNS json
      LANGUAGE sql
      IMMUTABLE
      STRICT
    AS $function$
    SELECT CASE
      WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json"
      ELSE
         CASE COALESCE(array_length("key_path", 1), 0)
             WHEN 0 THEN "json"
             WHEN 1 THEN "json_object_del_key"("json", "key_path"[l])
             ELSE "json_object_set_key"(
               "json",
               "key_path"[l],
               "json_object_del_path"(
                 COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
                 "key_path"[l+1:u]
               )
             )
           END
        END
      FROM array_lower("key_path", 1) l,
           array_upper("key_path", 1) u
    $function$;
    CREATE OR REPLACE FUNCTION "json_object_del_key"(
      "json"          json,
      "key_to_del"    TEXT
    )
      RETURNS json
      LANGUAGE sql
      IMMUTABLE
      STRICT
    AS $function$
    SELECT CASE
      WHEN ("json" -> "key_to_del") IS NULL THEN "json"
      ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
              FROM (SELECT *
                      FROM json_each("json")
                     WHERE "key" <> "key_to_del"
                   ) AS "fields")::json
    END
    $function$;
    CREATE OR REPLACE FUNCTION "json_object_update_key"(
      "json"          json,
      "key_to_set"    TEXT,
      "value_to_set"  anyelement
    )
      RETURNS json
      LANGUAGE sql
      IMMUTABLE
      STRICT
    AS $function$
    SELECT CASE
      WHEN ("json" -> "key_to_set") IS NULL THEN "json"
      ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
              FROM (SELECT *
                      FROM json_each("json")
                     WHERE "key" <> "key_to_set"
                     UNION ALL
                    SELECT "key_to_set", to_json("value_to_set")) AS "fields")::json
    END
    $function$;
    CREATE OR REPLACE FUNCTION "json_object_set_keys"(
      "json"          json,
      "keys_to_set"   TEXT[],
      "values_to_set" anyarray
    )
      RETURNS json
      LANGUAGE sql
      IMMUTABLE
      STRICT
    AS $function$
    SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
      FROM (SELECT *
              FROM json_each("json")
             WHERE "key" <> ALL ("keys_to_set")
             UNION ALL
            SELECT DISTINCT ON ("keys_to_set"["index"])
                   "keys_to_set"["index"],
                   CASE
                     WHEN "values_to_set"["index"] IS NULL THEN 'null'::json
                     ELSE to_json("values_to_set"["index"])
                   END
              FROM generate_subscripts("keys_to_set", 1) AS "keys"("index")
              JOIN generate_subscripts("values_to_set", 1) AS "values"("index")
             USING ("index")) AS "fields"
    $function$;

    CREATE OR REPLACE FUNCTION "json_object_set_key"(
      "json"          json,
      "key_to_set"    TEXT,
      "value_to_set"  anyelement
    )
      RETURNS json
      LANGUAGE sql
      IMMUTABLE
      STRICT
    AS $function$
    SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
      FROM (SELECT *
              FROM json_each("json")
             WHERE "key" <> "key_to_set"
             UNION ALL
            SELECT "key_to_set", to_json("value_to_set")) AS "fields"
    $function$;
    CREATE OR REPLACE FUNCTION "json_object_set_path"(
      "json"          json,
      "key_path"      TEXT[],
      "value_to_set"  anyelement
    )
      RETURNS json
      LANGUAGE sql
      IMMUTABLE
      STRICT
    AS $function$
    SELECT CASE COALESCE(array_length("key_path", 1), 0)
             WHEN 0 THEN to_json("value_to_set")
             WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set")
             ELSE "json_object_set_key"(
               "json",
               "key_path"[l],
               "json_object_set_path"(
                 COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
                 "key_path"[l+1:u],
                 "value_to_set"
               )
             )
           END
      FROM array_lower("key_path", 1) l,
           array_upper("key_path", 1) u
    $function$;

    usage:

    s1=# SELECT json_object_del_key ('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
                                     'foo'),
                json_object_del_path('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
                                     '{"foo","moe"}');
    
     json_object_del_key |          json_object_del_path
    ---------------------+-----------------------------------------
     {"hello":[7,3,1]}   | {"hello":[7,3,1],"foo":{"mofu":"fuwa"}}
  • 相关阅读:
    Centos6.8通过yum安装mysql5.7
    查看mysql已安装
    canal client leader
    es按时间段统计总数
    nginx负载
    es 查看mapping 设置max_result_window
    es 修改默认bool条件个数
    less
    Less配置环境
    JavaScript面向对象与原型
  • 原文地址:https://www.cnblogs.com/fangyuan303687320/p/5509854.html
Copyright © 2020-2023  润新知