I am trying to write a simple stored function in PostgreSQL .

1. This function will receive two parameters . (details jsonb & provider_details jsonb[] ) .

2. details jsonb example :- {"category": "wood"} and provider_details jsonb[] example is

{
    "category_type": "Wood",
    "category_type_id": 2
  },
  {
    "category_type": "Iron",
    "category_type_id": 3
  }

3 . What I want :- ( explanation is complicated (ignore it) but result looks easy to understand).

Explanation :

I need to check "category" of "detail" parameter(wood) exists or not in "provider_details" array of objects as "category_type"(wood ,iron ). If the category exists (or not) return two arrays . Array 1 containing all objects with same "category" of "details" and second array , with all objects of "provider_details" not matching "details" "category".

RESULT :- (I am expecting )

If function name is "get_matched_array" ,

SELECT get_matched_array(`{"category": "wood"}`:jsonb,{"category_type": "Wood","category_type_id": 2},{"category_type": "Iron","category_type_id": 3 }:: jsonb[])

should return 2 arrays :

array 1 : matched array(with 'wood'). = [{"category_type":"Wood","category_type_id": 2}]

array 2 : unmatched array ( without 'wood' , that means [{"category_type": "Iron","category_type_id": 3 } ]only ).

I am beginner to PostgreSQL. I know the basics. I tried to create function like:

CREATE OR REPLACE FUNCTION get_matched_array(
    details jsonb,
    provider_details jsonb[]
) RETURNS TABLE (matched jsonb[], unmatched jsonb[]) AS $$

    -- Declare the matched and unmatched variables with the jsonb[] data type
    DECLARE matched jsonb[];
    DECLARE unmatched jsonb[];

// I NEED THE LOGIC HERE ...

    -- Return the matched and unmatched arrays
    RETURN QUERY SELECT matched, unmatched;
END;
$$ LANGUAGE plpgsql;

I hope this is simple for most good PostgreSQL programmers .

CodePudding user response：

You can write this function with sql instead of plpgsql, it will be a bit faster :

CREATE OR REPLACE FUNCTION get_matched_array(
    details jsonb,
    provider_details jsonb[]
) RETURNS TABLE (matched jsonb[], unmatched jsonb[]) LANGUAGE sql IMMUTABLE AS $$
  SELECT array_agg(y.content) FILTER (WHERE y.content->'category_type' = details->'category')
       , array_agg(y.content) FILTER (WHERE y.content->'category_type' <> details->'category')
    FROM unnest(provider_details) AS y(content)
$$ ;

This query first expand the provider_details array into a set of rows thanks to the unnest function. Then we use the aggregate function array_agg in order to build the expecting results while filtering the rows according to your criteria on the json objects. See the manual for more details about the aggregate functions, their syntax, their list and the json/jsonb functions.

see the result in dbfiddle