Ygthkb

How to limit sight distance to 1 km

How to prevent cleaner from hanging my lock screen in Ubuntu 16.04

Does paint affect EMI ability of enclosure?

Could a phylactery of a lich be a mirror or does it have to be a box?

Can I write a book of my D&D game?

Caruana vs Carlsen game 10 (WCC) why not 18...Nxb6?

Am I a Rude Number?

Advice for a new journal editor

Why do stocks necessarily drop during a recession?

Why has the mole been redefined for 2019?

How to say "Brexit" in Latin?

Cookies - Should the toggles be on?

How to remove lines through the legend markers in ListPlot?

Blindfold battle as a gladiatorial spectacle - what are the tactics and communication methods?

Table formatting top left corner caption

Using only 1s, make 29 with the minimum number of digits

It took me a lot of time to make this, pls like. (YouTube Comments #1)

Can making a creature unable to attack after it has been assigned as an attacker remove it from combat?

Pronunciation of umlaut vowels in the history of German

Digits in an algebraic irrational number

Can I string the D&D Starter Set campaign into another module, keeping the same characters?

Why avoid shared user accounts?

Citing paywalled articles accessed via illegal web sharing

How to prevent users from executing commands through browser URL

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

Declare variable of table type in PL/pgSQLPostgreSQL - Return unique combinations of columns based on where clauseHow to get particular object from jsonb array in PostgreSQL?How to return value for key from a jsonb column?Update Postgres Table with Array of JSONHow do you concatenate JSONB columns in a PostgreSQL query to produce a JSONB object?Import poorly formatted CSV/JSON to SQL ServerIs there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object columnFlashcards and templatesLoop through slices of 2-dimensional array in plpgsql function?postgresql: how to define a JSONB column with default value

0

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.

postgresql json plpgsql

share|improve this question

edited 16 mins ago

Erwin Brandstetter

93.5k9180293

asked 44 mins ago

JaneLJaneL

1

New contributor

JaneL is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why the tag [sql-server]? I removed it. You only speak of Postgres ...
  
  – Erwin Brandstetter
  17 mins ago
  
  
  

  
  
  
  

add a comment | 

0

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.

postgresql json plpgsql

share|improve this question

edited 16 mins ago

Erwin Brandstetter

93.5k9180293

asked 44 mins ago

JaneLJaneL

1

New contributor

JaneL is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why the tag [sql-server]? I removed it. You only speak of Postgres ...
  
  – Erwin Brandstetter
  17 mins ago
  
  
  

  
  
  
  

add a comment | 

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.

postgresql json plpgsql

share|improve this question

edited 16 mins ago

Erwin Brandstetter

93.5k9180293

asked 44 mins ago

JaneLJaneL

1

New contributor

JaneL is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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

share|improve this question

edited 16 mins ago

Erwin Brandstetter

93.5k9180293

asked 44 mins ago

JaneLJaneL

1

New contributor

JaneL is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 16 mins ago

Erwin Brandstetter

93.5k9180293

asked 44 mins ago

JaneLJaneL

1

New contributor

JaneL is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 16 mins ago

Erwin Brandstetter

93.5k9180293

edited 16 mins ago

Erwin Brandstetter

93.5k9180293

asked 44 mins ago

JaneLJaneL

1

New contributor

JaneL is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 44 mins ago

JaneLJaneL

1

1 Answer
1

active

oldest

votes

0

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

CREATE OR REPLACE FUNCTION pg_temp.my_function(input jsonb)

  RETURNS jsonb

  LANGUAGE plpgsql AS  -- language declaration required

$func$

DECLARE

   _key   text;

   _value text;

BEGIN

    FOR _key, _value IN

      SELECT 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 pg_temp.my_function('{"a":1, "b":2, "c":3}');

Related:

• Declare variable of table type in PL/pgSQL

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 around the corner ...

share|improve this answer

edited 20 mins ago

answered 26 mins ago

Erwin BrandstetterErwin Brandstetter

93.5k9180293

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

JaneL is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231016%2floop-through-key-value-pairs-of-a-jsonb-object-in-postgresql-function%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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

CREATE OR REPLACE FUNCTION pg_temp.my_function(input jsonb)

  RETURNS jsonb

  LANGUAGE plpgsql AS  -- language declaration required

$func$

DECLARE

   _key   text;

   _value text;

BEGIN

    FOR _key, _value IN

      SELECT 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 pg_temp.my_function('{"a":1, "b":2, "c":3}');

Related:

• Declare variable of table type in PL/pgSQL

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 around the corner ...

share|improve this answer

edited 20 mins ago

answered 26 mins ago

Erwin BrandstetterErwin Brandstetter

93.5k9180293

add a comment | 

0

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

CREATE OR REPLACE FUNCTION pg_temp.my_function(input jsonb)

  RETURNS jsonb

  LANGUAGE plpgsql AS  -- language declaration required

$func$

DECLARE

   _key   text;

   _value text;

BEGIN

    FOR _key, _value IN

      SELECT 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 pg_temp.my_function('{"a":1, "b":2, "c":3}');

Related:

• Declare variable of table type in PL/pgSQL

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 around the corner ...

share|improve this answer

edited 20 mins ago

answered 26 mins ago

Erwin BrandstetterErwin Brandstetter

93.5k9180293

add a comment | 

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

CREATE OR REPLACE FUNCTION pg_temp.my_function(input jsonb)

  RETURNS jsonb

  LANGUAGE plpgsql AS  -- language declaration required

$func$

DECLARE

   _key   text;

   _value text;

BEGIN

    FOR _key, _value IN

      SELECT 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 pg_temp.my_function('{"a":1, "b":2, "c":3}');

Related:

• Declare variable of table type in PL/pgSQL

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 around the corner ...

share|improve this answer

edited 20 mins ago

answered 26 mins ago

Erwin BrandstetterErwin Brandstetter

93.5k9180293

CREATE OR REPLACE FUNCTION pg_temp.my_function(input jsonb)

  RETURNS jsonb

  LANGUAGE plpgsql AS  -- language declaration required

$func$

DECLARE

   _key   text;

   _value text;

BEGIN

    FOR _key, _value IN

      SELECT jsonb_each_text($1)

    LOOP

        -- do some math operation on its corresponding value

        RAISE NOTICE '%: %', _key, _value;

    END LOOP;



    RETURN input;

END

$func$;

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

share|improve this answer

edited 20 mins ago

answered 26 mins ago

Erwin BrandstetterErwin Brandstetter

93.5k9180293

answered 26 mins ago

Erwin BrandstetterErwin Brandstetter

93.5k9180293

answered 26 mins ago

Erwin BrandstetterErwin Brandstetter

93.5k9180293