parsing Json objects in array into relational objects and fields

Question

Hi I need to parse the json objects under operationsList array into relational fields and objects but the trick here is the sample I pasted had seven objects under the operationsList array, my code is able to parse each json object individually but U need to use functions that are able to select the each json object automatically.

I need the answer to execute in pgadmin $ for postgres 9.6

NOTE: I tried hours and hours using all the functions

CODE PASTED BELOW

{
  "header" : {
    "tasklistid" : "RC016753",
    "planningPlant" : "ECL",
    "status" : "Z03",
    "title" : "725307-STG 1 LPT NZZ",
    "parentRouterId" : "",
    "enginecode" : "5B",
    "structureNode" : "5B_72530015200",
    "routerType" : "INR",
    "disassemblyLocation" : "TL725300",
    "latestReleaseDate" : "31.12.8888",
    "planningReference" : "INR-725300"
  },
  "operationsList" : [ {
    "order" : "0010",
    "text" : "232-003-IN PROCESS - FPI",
    "workCenter" : "248",
    "controlKey" : "ZQP3",
    "details" : {
      "longText" : "232-003-IN PROCESS - FPI FPI : Floroscent penetration Inspection This is Long text to test the API",
      "MICS" : [ {
        "order" : "0010",
       "MICNumber" : "C0000100",
        "Plant" : "ECL",
        "version" : "000003"
      }, {
        "order" : "0020",
        "MICNumber" : "C0175399",
        "Plant" : "ECL",
        "version" : "000001"
      }, {
        "order" : "0030",
        "MICNumber" : "C0175400",
        "Plant" : "ECL",
        "version" : "000001"
      } ],
      "dependencies" : [ ],
      "documents" : [ {
        "Number" : "5B/ZSC-ECL-0041176",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0500"
      }, {
        "Number" : "5B/ZSC-ECL-0041175",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0510"
      }, {
        "Number" : "5B/ZSC-ECL-0041177",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0520"
      }, {
        "Number" : "5B/ZSC-ECL-0041178",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0530"
      } ]
    }
  }, {
    "order" : "0020",
    "text" : "307-004-BENCHING",
    "workCenter" : "358",
    "controlKey" : "SM01",
    "details" : {
      "longText" : "",
      "MICS" : [ ],
      "dependencies" : [ ],
      "documents" : [ ]
    }
  }, {
    "order" : "0030",
    "text" : "110-005-CHEMICAL CLEANING",
    "workCenter" : "048",
    "controlKey" : "ZQP1",
    "details" : {
      "longText" : "",
      "MICS" : [ {
        "order" : "0010",
        "MICNumber" : "C0006683",
        "Plant" : "ECL",
        "version" : "000001"
      } ],
      "dependencies" : [ ],
      "documents" : [ ]
    }
  }, {
    "order" : "0040",
    "text" : "310-001-TIG WELDING",
    "workCenter" : "295",
    "controlKey" : "ZQP1",
    "details" : {
      "longText" : "",
      "MICS" : [ {
        "order" : "0010",
        "MICNumber" : "C0020962",
        "Plant" : "ECL",
        "version" : "000001"
      }, {
        "order" : "0020",
        "MICNumber" : "C0006552",
        "Plant" : "ECL",
        "version" : "000001"
      }, {
        "order" : "0030",
        "MICNumber" : "C0006511",
        "Plant" : "ECL",
        "version" : "000001"
      } ],
      "dependencies" : [ ],
      "documents" : [ {
        "Number" : "5B/ZSC-ECL-0041175",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0500"
      }, {
        "Number" : "5B/ZSC-ECL-0041176",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0510"
      }, {
        "Number" : "5B/ZSC-ECL-0041177",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0520"
      }, {
        "Number" : "5B/ZSC-ECL-0041178",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0530"
      } ]
    }
  }, {
    "order" : "0050",
    "text" : "307-005-BENCHING",
    "workCenter" : "358",
    "controlKey" : "SM01",
    "details" : {
      "longText" : "",
      "MICS" : [ ],
      "dependencies" : [ ],
      "documents" : [ {
        "Number" : "5B/ZSC-ECL-0041175",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0500"
      }, {
        "Number" : "5B/ZSC-ECL-0041176",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0510"
      }, {
        "Number" : "5B/ZSC-ECL-0041177",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0520"
      }, {
        "Number" : "5B/ZSC-ECL-0041178",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0530"
      } ]
    }
  }, {
    "order" : "0060",
    "text" : "220-001-IN PROCESS DIMENSIONAL INSP",
    "workCenter" : "358",
    "controlKey" : "ZQP1",
    "details" : {
      "longText" : "",
      "MICS" : [ {
        "order" : "0010",
        "MICNumber" : "C0027739",
        "Plant" : "ECL",
        "version" : "000001"
      } ],
      "dependencies" : [ ],
      "documents" : [ {
        "Number" : "5B/ZSC-ECL-0041175",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0500"
      }, {
        "Number" : "5B/ZSC-ECL-0041176",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0510"
      }, {
        "Number" : "5B/ZSC-ECL-0041177",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0520"
      }, {
        "Number" : "5B/ZSC-ECL-0041178",
        "Type" : "ZSC",
        "version" : "00",
        "order" : "0530"
      } ]
    }
  }, {
    "order" : "0070",
    "text" : "110-006-CHEMICAL CLEANING",
    "workCenter" : "048",
    "controlKey" : "ZQP1",
    "details" : {
      "longText" : "",
      "MICS" : [ {
        "order" : "0010",
        "MICNumber" : "C0006683",
        "Plant" : "ECL",
        "version" : "000001"
      } ],
      "dependencies" : [ ],
      "documents" : [ ]
    }
  } ]
}

Please let me know the right way to do this and feel free to post your thoughts.

Edit your question: 1. Tag a language 2. Select all the code and hit Ctrl-K. — 001, Mar 16 '17 at 13:45
What do you mean be *relational fields and objects*? Do you already have a schema? ([`json[b]_populate_recordset()`](https://www.postgresql.org/docs/current/static/functions-json.html)) -- Or you want to create a schema for your JSON input on-the-fly? (seems pretty risky) — pozs, Mar 16 '17 at 14:55
I already had a schema for relational tables and fields the column names are "text" "workCenter" "controlKey" "details" all I need to do is parse the column values from json objects in the array to insert into those table columns. — sreecharan rao yadagiri, Mar 16 '17 at 15:40
@sreecharanraoyadagiri Please post your schema then. If `details` is `json` or `jsonb` typed column, then (the previously linked) `json[b]_populate_recordset()` function does just like that. if `details` is a foreign key, which points to some other table, you'll have to do more than just a single function call though. — pozs, Mar 16 '17 at 15:51
@pozs posted my try below please check it, let me know if can add anything — sreecharan rao yadagiri, Mar 16 '17 at 17:30

Akash Jagtap · Answer 1 · 2017-03-16T14:08:00.770

0

ArrayList<operationsList> objOperationsList=new ArrayList<>();

class operationsList

{

String order;

String text;

String workCenter;

String controlKey;

Details objDetails;

}

class Details

{

   String longText;

ArrayList<MICS> objMICS=new ArrayList<>();

   ArrayList<Dependencies> objDependencies=new ArrayList<>();

   ArrayList<Documents> objDocuments=new ArrayList<>();

}

class MICS {}

class Dependencies {}

class Documents {}

edited Mar 16 '17 at 14:08

answered Mar 16 '17 at 14:00

Akash Jagtap

404
2
8

This is not right answer i think for executing in the database pl/pgsql – sreecharan rao yadagiri Mar 16 '17 at 14:29

score 0 · Answer 2 · answered Mar 16 '17 at 17:29

its JSON column, not JSONB I had this entire JSON code (pasted above) in the JSON column and I have operation table where columns are order, workcenter, controlkey, text in the table so I need select the values from the JSON object and insert those values into the columns so I am able to do that using this query

INSERT INTO public.operation(
 order_1, text, workcenter_number, control_key, long_text)
  (select

router_payload::json #> '{operationsList, 5}'->> 'order', router_payload ::json #> '{operationsList, 5}' ->> 'text',

router_payload::json #> '{operationsList, 5}'->> 'workCenter',

router_payload::json #> '{operationsList, 5}' ->> 'controlKey', router_payload #> '{operationsList, 5}'-> 'details' ->>'longText',

from public.router_data );

BUT MY QUESTION if you see in the query I am specifying the {operation,5} I don't want to give that I trying to make it as looping or something it needs to select all the operation when i execute the query

If you further specify your question, please edit the question itself, instead of posting it as an answer. — pozs, Mar 17 '17 at 08:45
And please, post your complete schema. We can just guess without it. — pozs, Mar 17 '17 at 08:46

parsing Json objects in array into relational objects and fields

2 Answers2