title: LOOP through JSON data in stored procedure Postgres
body: <p>MY fpo table looks like below</p>
  
  <pre><code> id                             fo_data
    1          {"bene_first_name":{"value":"Chris1"},"bene_last_name":{"value":"Ronald"}}
    2          {"bene_first_name":{"value":"John"},"bene_last_name":{"value":"Wick"}}
    1          {"bene_first_name":{"value":"James"},"bene_last_name":{"value":"Cooper"}}
  </code></pre>
  
  <p>I have created a stored procedure as below and i am trying to push all the data of particular column "fo_data" into single JSON variable and I am trying to loop the obtained JSON data </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION file_compare()
  RETURNS TEXT LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
  DECLARE
    fpo_data jsonb;
    i JSONB;
  BEGIN
    SELECT json_agg((fpdata))::jsonb
    FROM (SELECT fo_data AS fpdata
          FROM fpo LIMIT 100
      ) t  INTO fpo_data; 
    FOR i IN SELECT * FROM jsonb_array_elements(fpo_data) LOOP
      RAISE NOTICE 'output from space %', i-&gt;&gt;'bene_firstname';
    END LOOP;
    RETURN fpo_data;
  END;
  $BODY$;
  </code></pre>
  
  <p>I am getting the below output for <code>"RETURN fpo_data"</code></p>
  
  <pre><code> ["{\"bene_first_name\":{\"value\":\"Chris\"},\"bene_last_name\":{\"value\":\"Ronald\"}}",
    "{\"bene_first_name\":{\"value\":\"John\"},\"bene_last_name\":{\"value\":\"Wick\"}}",
     "{\"bene_first_name\":{\"value\":\"James\"},\"bene_last_name\":{\"value\":\"Cooper\"}}"
  </code></pre>
  
  <p>If i try to print <code>'bene_firstname'</code> like this </p>
  
  <pre><code> RAISE NOTICE 'output from space %', i-&gt;&gt;'bene_firstname'
  </code></pre>
  
  <p>I am getting the error as </p>
  
  <pre><code> ERROR: operator does not exist: record -&gt;&gt; unknown.
  </code></pre>
  
  <p>If I print "i" alone I am getting the below data printed in console</p>
  
  <pre><code>NOTICE:  output from space ("""{\\""bene_first_name\\"":{\\""value\\"":\\""Chris90\\""},\\""bene_last_name\\"":{\\""value\\"":\\""Ronald\\""}}""")
  NOTICE:  output from space ("""{\\""bene_first_name\\"":{\\""value\\"":\\""John\\""},\\""bene_last_name\\"":{\\""value\\"":\\""Wick\\""}}""")
  NOTICE:  output from space ("""{\\""bene_first_name\\"":{\\""value\\"":\\""James\\""},\\""bene_last_name\\"":{\\""value\\"":\\""Cooper\\""}}""")
  </code></pre>
  
  <p>How to I get 'bene_firstname' from the above json array</p>