How to write a PostgreSQL stored function to alter jsonb array?

Question

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

This function will receive two parameters . (details jsonb & provider_details jsonb[] ) .
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 .

Edouard · Accepted Answer · 2022-12-30 11:01:41Z

0

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

answered Dec 30, 2022 at 11:01

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

noobdev6576576 Over a year ago

I made a mistake , second argument is array [ ] , how can I call /select this function ?. I tried to call the function like "SELECT get_matched_array('{"category": "wood"}','[{"category_type": "Wood","category_type_id": 2},{"category_type": "Iron","category_type_id": 3 }]') " . But I am finding "malformed array literal" error ? What changes should I make to this function ? @Edouard

Edouard Over a year ago

the right syntax is in dbfiddle. Your syntax corresponds to a json array of type json, which is different from an array of json of type json[]

noobdev6576576 Over a year ago

This is very accurate answer . Where can I learn more about PSQL stored functions ? Resources are very limited when I googled ? I want to learn loops, cases , logical operations , conditions ,and other important things. I am referring your above links too . @Edouard

noobdev6576576 Over a year ago

This is correct answer . Precise @Edouard

Edouard Over a year ago

everything is explained in the manual which is like the postgres bible. You will need some time to read, understand, and remember everything ... Take your time.

Collectives™ on Stack Overflow

How to write a PostgreSQL stored function to alter jsonb array?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related