Extract data from dynamic JSON from PostgreSQL

Question

Below is my JSON:

{
   "Code" : "TrafficFlowStat",
   "FlowStates" : [
      {
         "AverageSpeed" : 52.0,
         "DetailInfo" : {
            "AverageSpeed" : 52.0,
            "BackOfQueue" : 0.0,
            "Direction" : [ "Straight" ],
            "DrivingDirection" : [ "Approach", "", "" ],
            "FinalVehicleDist" : 21474836.0,
            "FlowRate" : 3600,
            "JamState" : "Slowed",
            "Lane" : 1,
            "LargeVehicles" : 0,
            "LongVehicles" : 0,
            "MachineAddress" : "",
            "MachineName" : "12312313435435",
            "MediumVehicles" : 0,
            "MotoVehicles" : 0,
            "Period" : 0,
            "PeriodByMili" : 1000,
            "SmallVehicles" : 1,
            "SpaceHeadway" : 0.0,
            "SpaceOccupyRatio" : 38.0,
            "TimeHeadway" : 0.0,
            "TimeOccupyRatio" : 79.0,
            "UTC" : 1561657570,
            "UTCMS" : 68,
            "VehicleTypeFlow" : {
               "SaloonCarVehicles" : 1,
               "Trucks" : 2,
               "MotorCycle" : 5
            },
            "Vehicles" : 1,
            "Volume" : 1
         },
         "DrivingDirection" : [ "Approach", "", "" ],
         "Flow" : 1,
         "JamState" : "Slowed",
         "Lane" : 1,
         "Period" : 0,
         "PeriodByMili" : 1000,
         "State" : 3
      },
      {
         "AverageSpeed" : -1.0,
         "DetailInfo" : {
            "AverageSpeed" : -1.0,
            "BackOfQueue" : 0.0,
            "Direction" : [ "Straight" ],
            "DrivingDirection" : [ "Approach", "", "" ],
            "FinalVehicleDist" : 0.0,
            "FlowRate" : 0,
            "JamState" : "Clear",
            "Lane" : 2,
            "LargeVehicles" : 0,
            "LongVehicles" : 0,
            "MachineAddress" : "",
            "MachineName" : "56756345345345",
            "MediumVehicles" : 0,
            "MotoVehicles" : 0,
            "Period" : 0,
            "PeriodByMili" : 1000,
            "SmallVehicles" : 0,
            "SpaceHeadway" : -0.0,
            "SpaceOccupyRatio" : 0.0,
            "TimeHeadway" : 0.0,
            "TimeOccupyRatio" : 0.0,
            "UTC" : 1561657570,
            "UTCMS" : 68,
            "VehicleTypeFlow" : {},
            "Vehicles" : 0,
            "Volume" : 0
         },
         "DrivingDirection" : [ "Approach", "", "" ],
         "Flow" : 0,
         "JamState" : "Clear",
         "Lane" : 2,
         "Period" : 0,
         "PeriodByMili" : 1000,
         "State" : 3
      },
      {
         "AverageSpeed" : -1.0,
         "DetailInfo" : {
            "AverageSpeed" : -1.0,
            "BackOfQueue" : 0.0,
            "Direction" : [ "Straight" ],
            "DrivingDirection" : [ "Approach", "", "" ],
            "FinalVehicleDist" : 21474836.0,
            "FlowRate" : 0,
            "JamState" : "Clear",
            "Lane" : 3,
            "LargeVehicles" : 0,
            "LongVehicles" : 0,
            "MachineAddress" : "",
            "MachineName" : "6345r2341342545",
            "MediumVehicles" : 0,
            "MotoVehicles" : 0,
            "Period" : 0,
            "PeriodByMili" : 1000,
            "SmallVehicles" : 0,
            "SpaceHeadway" : -0.0,
            "SpaceOccupyRatio" : 12.0,
            "TimeHeadway" : 0.0,
            "TimeOccupyRatio" : 0.0,
            "UTC" : 1561657570,
            "UTCMS" : 68,
            "VehicleTypeFlow" : {},
            "Vehicles" : 0,
            "Volume" : 0
         },
         "DrivingDirection" : [ "Approach", "", "" ],
         "Flow" : 0,
         "JamState" : "Clear",
         "Lane" : 3,
         "Period" : 0,
         "PeriodByMili" : 1000,
         "State" : 3
      }
   ],
   "Name" : "TrafficFlowStat1",
   "Sequence" : 1,
   "UTC" : 1561657570,
   "UTCMS" : 68
}

There are dynamic multiple blocks in this json, there could be multiple "DetailInfo" blocks. And in "DetailInfo" blocks, there are multiple vehicles in "VehicleTypeFlow" block.

I need all vehicle count in all "DetailInfo" blocks in PostgreSQL. Also need "Lane" and "Vehicles" in "DetailInfo" block.

There could be multiple "DetailInfo" block in JSON.

Output:

UTC                 | Lane | SaloonCarVehicles | Trucks | MotorCycle
28-06-2019 12:45:20 | 1    | 1                 | 2      | 5
28-06-2019 12:45:20 | 2    | 0                 | 0      | 0
28-06-2019 12:45:20 | 3    | 0                 | 0      | 0

Hardik · Accepted Answer · 2019-06-28 07:22:00Z

1

Hello shwetank your question could be answer as below

SELECT
        (data->>'Code'),
        (data->>'UTC'),
        (json_array_elements(data->'FlowStates'))->'DetailInfo'->'Lane' as Lane,
        (json_array_elements(data->'FlowStates'))->'DetailInfo'->'VehicleTypeFlow'->'SaloonCarVehicles' as Vehicle
    FROM cte;

where data is your json

answered Jun 28, 2019 at 7:22

Hardik

243 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract data from dynamic JSON from PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related