Nested loop over selected row_to_json result-CodePudding

I am trying to do a nested loop on the result of row_to_json() but getting below error.

DO
$BODY$
DECLARE
    js jsonb := '{"ddd525b9-e47c-46ac-ab55-e0db93338ff3": "ddd525b9-e47c-46ac-ab55-e0db93338ff4", "550e8400-e29b-41d4-a716-446655440000": "e15c6fe3-000c-4c69-9c41-9e241323cbe5", "0ba26163-bf75-40d2-8526-172229604d95": "7ed83dbc-3895-490c-8a16-9842cfe85e28"}';
    i record;
BEGIN
  FOR i IN SELECT * FROM jsonb_each_text(js)
  loop
    DECLARE
    orgs jsonb := (SELECT row_to_json(t) FROM (Select parent_id from my_schema.my_table o where o.id != i.value) t);
    j record;
    for j in select * from jsonb_each_text(orgs)
    loop
      RAISE NOTICE 'key %', j.key;
      RAISE NOTICE 'value %', j.value;
    end loop;
  END LOOP;
END;
$BODY$;

Error:

SQL Error [42601]: ERROR: syntax error at or near "for"
  Position: 506

CodePudding user response：

You forgot the BEGIN and END around your second loop.

DO
$do$
DECLARE
    js jsonb := '{"ddd525b9-e47c-46ac-ab55-e0db93338ff3": "ddd525b9-e47c-46ac-ab55-e0db93338ff4", "550e8400-e29b-41d4-a716-446655440000": "e15c6fe3-000c-4c69-9c41-9e241323cbe5", "0ba26163-bf75-40d2-8526-172229604d95": "7ed83dbc-3895-490c-8a16-9842cfe85e28"}';
    i record;
BEGIN
  FOR i IN (SELECT * FROM jsonb_each_text(js)) LOOP
    DECLARE
      orgs jsonb := (SELECT row_to_json(t) FROM (Select parent_id from my_schema.my_table o where o.id != i.value) t);
      j record;
    BEGIN
      FOR j IN (select * from jsonb_each_text(orgs)) LOOP
        RAISE NOTICE 'key %', j.key;
        RAISE NOTICE 'value %', j.value;
      END LOOP;
    END;
  END LOOP;
END;
$do$;