How to filter JSON array elememts by type of value?-CodePudding

Postgres 12, column contains sample json from Quill Editor like:

{
    "ops": 
    [
    {
        "insert": "Text 1"
    }, 
    {
        "insert": {"video":"..."}
    },
    {
        "insert": "Text 2"
    }, 
    {
        "insert": {"image":"data:image/jpeg;base64,..."}
    }]
}

To get result as Text 1 Text 2, meaning filter out (ignore/skip) all "insert": {"video":"..."} and "insert": {"image":"..."} if presented.

Operators like checking ::json ?| array['video', 'image'] or deleting ::jsonb-'video' seem helpful, but pg doesn't do one-line if-then-else (condition)?true_value:false_value. How to utilize them in this function?

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;
end;
$BODY$;

CodePudding user response：

It depends on the way you want to identify and select both elements {"insert": "Text 1"} and {"insert": "Text 2"} from the json array value associated to the key "ops" :

Identify and select the elements by their position in the json array :

  return j#>>'{ops,0,insert}' || ' ' || j#>>'{ops,2,insert}' ;

Identify and select the elements by their json type which is 'string' :

  return string_agg(obj->>'insert', ' ')
  FROM json_array_elements(j->'ops') obj
  WHERE json_typeof(obj) = 'string' ;

Identify and select the elements by their json type which is not 'object' :

  return string_agg(obj->>'insert', ' ')
  FROM json_array_elements(j->'ops') obj
  WHERE NOT json_typeof(obj) = 'object' ;

This last example do not select the elements whose value is a json object whatever the "key" and "value" inside this json object are.

CodePudding user response：

CREATE OR REPLACE FUNCTION public.fun(j json)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT string_agg(elem ->> 'insert', ' ')
FROM   json_array_elements(j -> 'ops') elem
WHERE  json_typeof(elem->'insert') <> 'object';
$func$;

db<>fiddle here

After unnesting with json_array_elements() like you already had, check the type of the value for the property name 'insert' with json_typeof() (not for the nesting object which is always an object).

I use a simple SQL function for the simple task.