1

I have a JSON and I need to write the values into different tables. I could get the data from json, but I need to insert the data accordingly. It's like I have a form, the form has n number of sections, each section have n number of steps and each step can have n number of questions. How I can loop this and write into different tables? Basically I need to know how we can find how many sections, steps and questions we have in the JSON. I tried array_length, but not working.

Here is a small sample of my JSON.

 {  "functionId" : "2","subFunctionId" : "6","groupId" : "11","formId" : "","formName":"BladeInseption","submittedBy" : "200021669","createdDate" : "2015-08-06",    
    "updatedBy" : "","updatedDate" : "","comments" : "","formStatusId" :"11","formStatus" :"Draft","formLanguage" : "English","isFormConfigured" : "N","formChange":"Yes",
    "sectionLevelChange":"Yes","isActive" : "Y","formVersionNo" : "1.0","formFooterDetails" : "","formHeaderDetails" : "","images" : [
    {"imageId" : "","imageTempId" : "","imageTempUrl" : "","imageName" : "","imageUrl" : "","isDeleted" : "","imagesDesc" : ""} ],  
    "imagesDescLevel" : "","sectionElements" : [{"sectionElement":[{"sectionId" : "","sectionTempId":"sectionId+DDMMHHSSSS","sectionName":"section1",
    "sectionChange":"Yes","stepLevelChange":"Yes","sectionLabel" : "","sectionOrder" : "1","outOfScopeSection" : "false",
    "punchListSection" : "false","images" : [{"imageId" : "","imageTempId" : "","imageTempUrl" : "","imageName" : "","imageUrl" : "","isDeleted" : "",
    "imagesDesc" : ""}],"imagesDescLevel" : "","isDeleted" : "","stepElements" : [{"stepElement":[{"stepId" : "","stepTempId":"stepId+DDMMHHSSSS",
    "stepName":"section1step1","stepLabel" : "","stepOrder" : "1","stepChange":"Yes","questionLevelChange":"Yes","images" : [{"imageId" : "",
    "imageTempId" : "","imageTempUrl" : "","imageName" : "","imageUrl" : "","isDeleted" : "","imagesDesc" : ""}],"imagesDescLevel" : "","isDeleted" : "",
    "questionAnswerElements" : [{"questionAnswerElement":[{"questionId" : "","questionClientUid" : "","questionDescription" : "step1question1",
    "questionAccessibility" : "","isPunchListQuestion" : "","questionChange":"Yes","questionOrder" : "1","isDeleted" : "","images" : [{
    "imageId" : "","imageTempId" : "","imageTempUrl" : "","imageName" : "","imageUrl" : "","isDeleted" : "","imagesDesc" : ""}],"imagesDescLevel" : "",     
    "answerId" : "","answerClientUid" : "","elements" :[{"element" :[{"elementId": "2","elementMapId" : "12","clientUid" : "","clientClass" : "","imageTempId" : "",
    "imageTempUrl" : "","elementType":"Question","elementOrder" : "1","elementArributuesProp": [{"attributeId" : "1","attributeName" : "","defaultValue" : ""}],
    "elementArributuesVal":[{"value1" : "item1"}],"rule" : [{"ruleId" : "1","ruleName" : "Mandatory","formula" : "i>a","formulaData" : "i>50","isDeleted" : "",
...
}
Patrick
  • 29,357
  • 6
  • 62
  • 90
Rose
  • 101
  • 1
  • 2
  • 9

1 Answers1

0

If you know all paths to JSON arrays in your code, can use some special functions appearing in 9.4 such as

SELECT json_array_length('{"array":[{"a":1},{"b":2},{"c":3}]}'::json->'array')

If you need to iterate through JSON array, there is another useful function:

SELECT json_array_elements('{"array":[{"a":1},{"b":2},{"c":3}]}'::json->'array')
SELECT json_array_elements('[{"a":1},{"b":2},{"c":3}]'::json)

or if json is stored in table, lets call

SELECT json_array_elements(tbl.json_value->'array') FROM jsontable AS tbl

It returns a set of json values unwrapped from array ready to processing. http://www.postgresql.org/docs/9.4/static/functions-json.html

More information about JSON parsing can be found here How do I query using fields inside the new PostgreSQL JSON datatype?

Community
  • 1
  • 1
begemoth
  • 321
  • 3
  • 10
  • Hi, I inserted the json in a table and table name id tbl and the column name is j. so how can I use this json_array_length and store the value in a variable so that I can use this value to iterate. – Rose Sep 22 '15 at 08:25