postgres9.4 loop through jsonb: how select vals of key:val pairs

Question

This plpgsql script selects the keys from a set of jsonb object key:value pairs, but how to select the value for each pair?

DO
$BODY$
DECLARE
    js jsonb := '{"a": "1", "b": "2", "c": "3"}';
    i text;
BEGIN
  FOR i IN SELECT * FROM jsonb_each_text(js)
  LOOP
    RAISE NOTICE 'key %', i;
    --RAISE NOTICE 'value %', i.value; <--fai
  END LOOP;
END;
$BODY$;

Should be possible as man page http://www.postgresql.org/docs/9.4/static/functions-json.html indicates return value is setof key text, value text. This post Postgres - array for loop answers the question for array. Also tried jsonb_each() and jsonb_array_elements() changing iterator to jsonb, with error "Cannot extract element from an object"

score 11 · Accepted Answer · answered Apr 27 '15 at 06:43

As you declared i as text it only contains the first column. Declare it as RECORD:

DO
$BODY$
DECLARE
    js jsonb := '{"a": "1", "b": "2", "c": "3"}';
    i record;
BEGIN
  FOR i IN SELECT * FROM jsonb_each_text(js)
  LOOP
    RAISE NOTICE 'key %', i.key;
    RAISE NOTICE 'value %', i.value;
  END LOOP;
END;
$BODY$;

postgres9.4 loop through jsonb: how select vals of key:val pairs

1 Answers1