如何在PostgreSQL 12中按值类型过滤JSON数组元素提取纯文本

方案1：过滤所有非字符串类型的insert值（适配Quill默认场景） #

Quill编辑器中纯文本内容的insert值均为字符串类型，图片、视频等富媒体的insert值为对象类型，直接加过滤条件即可实现需求：

CREATE OR REPLACE FUNCTION public.fun(
    j json)
    RETURNS text
    LANGUAGE 'plpgsql' immutable
AS $BODY$
begin
  return string_agg((obj->>'insert') || ' ', '')
  FROM json_array_elements(j->'ops') obj
  -- 仅保留insert值为字符串的元素
  WHERE json_typeof(obj->'insert') = 'string';
end;
$BODY$;

json_typeof是PostgreSQL内置的JSON类型判断函数，返回值包括string、number、boolean、object、array、null，这里用来快速筛选纯文本内容。

方案2：仅过滤带image/video键的对象（保留其他自定义对象内容） #

如果后续需要保留除了图片、视频之外的其他对象类型insert内容，可以用PostgreSQL的CASE表达式实现类三目运算的效果：

CREATE OR REPLACE FUNCTION public.fun(
    j json)
    RETURNS text
    LANGUAGE 'plpgsql' immutable
AS $BODY$
begin
  return string_agg(
    CASE 
      WHEN json_typeof(obj->'insert') = 'object' AND (obj->'insert')::jsonb ?| array['video','image'] 
      THEN NULL 
      ELSE obj->>'insert' || ' ' 
    END, '')
  FROM json_array_elements(j->'ops') obj;
end;
$BODY$;

这里?|运算符用来判断jsonb对象是否包含指定数组中的任意一个键，精准识别图片、视频类富媒体元素并过滤。

内容的提问来源于stack exchange，提问作者Jeb50