Question

How to extract value from json and insert record in Postgres DB?

  • 26 March 2022
  • 1 reply
  • 13 views

In my case, I have JSON object that contains table and it values in array. I am trying to extract each table object from JSON and insert it features(columns) array values into postgresql table based on table name. The json structure will change every time and it is dynamic.

Input:

{

  {

    "table": "Table1",

    "key": "2343",

    "features": [

      {

        "status": "Finished",

        "id": "1234",

        "kind": "Unknown",

        "project_name": "XXXXXX"

      }

    ]

  }

  {

    "table": "Table2",

    "key": "2667190",

    "features": [

      {

        "column1": "test",

        "column2": "A",

        "status": "Unknown",

        }

    ]

  },

}

 

Expected output:

image


1 reply

Badge +2

@ganeshmoorthim​ The FME JSON reader should be able to handle this structure OK and flatten the attributes. If you use either the FeatureReader or the SchemaScanner, you can dig out the schema of the feature and then use Dynamic writer capabilities on the Postgres writer to create your tables.

Reply