Postgresql – Loop through key/value pairs of a jsonb object in postgresql function

jsonplpgsqlpostgresql

I am trying to create a function in Postgres that can loop through each key/value pair in a given jsonb object.

create or replace function myFunction
(input jsonb)
returns jsonb as $$
BEGIN

    // foreach(key in input)
    //       do some math operation on its corresponding value

    returns input;

END; $$

The argument input is expected to be a jsonb object, such as {"a":1, "b":2, "c":3}.

I want to loop through each key/value pair in the object. As you can tell from my comment, I used to write backend code with more general-purpose programming languages like c, java, etc. So I am not good at SQL. I have tried to search online, but they talk about how to loop through jsonb array instead of object. So really stuck here.

Best Answer

Use jsonb_each(jsonb) or jsonb_each_text(jsonb) in a FOR loop like:

CREATE OR REPLACE FUNCTION my_function(input jsonb)
  RETURNS jsonb
  LANGUAGE plpgsql AS  -- language declaration required
$func$
DECLARE
   _key   text;
   _value text;
BEGIN
    FOR _key, _value IN
       SELECT * FROM jsonb_each_text($1)
    LOOP
       -- do some math operation on its corresponding value
       RAISE NOTICE '%: %', _key, _value;
    END LOOP;

    RETURN input;
END
$func$;

Call:

SELECT my_function('{"a":1, "b":2, "c":3}');

Related:

Note that for many problems where people used to programming languages like c, java, etc. would tend to use a loop, there is a superior set-based solution in SQL around the corner. Side-by-side code examples:

Related Question