0

Being a novice to Postgres and SQL in general, I have the following scenario in Postgres 9.6:

table1

"Myjson" JSON
"DateOfAcquisition"  DATE
"Id" INT

In "Myjson" there is a list which I iterate trough with a FOR loop.

My objective is to place the elements of each json list in table1 in another table:

table2

jsonelem1 INT
jsonelem2 INT
"DateOfAcquisition" DATE
"Id" INT

I have written the following code for parsing the json field but I am not sure how to run the for loop for each record of the first table.

    DO
$BODY$
DECLARE
    omgjson json := myjsonfield; -- this should change for every row of the first table
    i json;
    myJsonelem1 INT;
    myJsonelem2 INT;

begin

FOR i IN SELECT * FROM json_array_elements(omgjson)
  loop
    myJsonelem2 i->>  'jsonsubfield'::INT;

    INSERT INTO destinationTable VALUES (myJsonelem2,DateOfAcquisition);
END LOOP;

END;
$BODY$ language plpgsql
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jpk
  • 101
  • 3
  • Please **[edit]** your question and add some sample JSON data and the result based on that data.[**Formatted text**](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). [Edit] your question do not post code in comments –  May 27 '18 at 13:56
  • Surround the `FOR` loop with an outer `FOR` loop that loops through the table: `FOR var1, var2, ... IN SELECT ... FROM ... LOOP`. – Laurenz Albe May 27 '18 at 14:33
  • Thanks Lawrence, that is what I did in the end. I was really wondering if there was a better way to do it. In the answer the double for loop. – Jpk May 28 '18 at 20:23
  • Your 1st "TABLE2" was supposed to be "TABLE1", i assume, and took the liberty to fix the question accordingly. – Erwin Brandstetter May 29 '18 at 01:19
  • A sample JSON value to test would help this question a lot. – Erwin Brandstetter May 29 '18 at 01:25

2 Answers2

1

A set-based approach is typically much faster (and shorter and less error prone) than looping.

Building on the setup of your answer, which is largely different from the one in your question.

INSERT INTO table2(myfield, data)  -- cleaner: explicit target columns
SELECT k."myField", j.i ->> 'data'
FROM   table1 k, json_array_elements(k."ScrapedJson" -> 'calendar_days') j(i)
WHERE  NOT k."HasBeenProcessed"
-- ORDER BY ???  -- you might want to order rows favorably?

The implicit LATERAL join is the key technique here.

... FROM   table1 k, json_array_elements(...) ...

is short for:

... FROM   table1 k
    CROSS  JOIN LATERAL json_array_elements(...) ...

Obviously, "ScrapedJson" -> 'calendar_days' is a JSON array, not a "list" like you wrote in the question.

Related:

Asides

Do yourself a favor and avoid double-quoted CaMeL-case names in Postgres. A wild mix of quoted and unquoted identifiers in your question and answer only serves to confuse. I quoted column names in your question to match your answer somewhat. My standing advise: Use legal, lower-case, unquoted names exclusively.

Consider the data type jsonb instead of json for most workloads.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Thanks to Lawrence here is the solution for anybody interested.

I wonder, is there a better way to this?

DO
$BODY$
DECLARE
    i json;
    k RECORD;
    mydata INT;
begin

FOR k IN SELECT * from TABLE1 where "HasBeenProcessed" = FALSE
    LOOP  

            FOR i IN SELECT * FROM json_array_elements(k."ScrapedJson" -> 'calendar_days')
              LOOP

                mydata = i->> 'data';

                INSERT INTO "Table2" VALUES (k."myField",mydata);

            END LOOP;


end LOOP;


END;
$BODY$ language plpgsql
Jpk
  • 101
  • 3