12

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.

1
  • What are you trying to do with those values inside the loop? What's wrong with SELECT jsonb_each_text('{"a":1, "b":2, "c":3}') Mar 1, 2019 at 8:19

1 Answer 1

14

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:

4
  • Thank you for your reply! Mind if I ask what is the set-based solution? @Erwin Brandstetter
    – JaneL
    Mar 1, 2019 at 5:12
  • 1
    @JaneL: I added side-by-side code examples above to clarify. And I fixed the sneaky bug in my demo that sparked dba.stackexchange.com/q/231068/3684 Mar 1, 2019 at 17:14
  • Excellent example. What $1 refers to?
    – Jeb50
    Oct 11, 2021 at 20:42
  • @Jeb50: Function parameters can be referred to by name (if one is given) or by ordinal position with $nnotation. So $1 refers to the first function parameter. Oct 11, 2021 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.