Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table a_table (
  id int not null primary key,
  jdata jsonb
);

insert into a_table values (1, '{
  "header": {
    "hardwareId": 0,
    "Id": "xxx",
    "manufacturerId": "",
    "timestampMsg": "xxx"
  },

  "Info": [{
    "timestampPos": "2022-06-30T01:00:00",
    "coordinate": {
      "latitude": 70,
      "longitude": -150
    }
  },
  {
    "timestampPos": "2022-06-30T02:00:00",
    "coordinate": {
      "latitude": 65,
      "longitude": -140
    }
  },  
 {
    "timestampPos": "2022-06-30T03:00:00",
    "coordinate": {
      "latitude": 60,
      "longitude": -135
    }
 },
 {
    "timestampPos": "2022-06-30T04:00:00",
    "coordinate": {
      "latitude": 55,
      "longitude": -130
    }
 }
 ]
}'::jsonb);

select * from a_table;

CREATE TABLE



INSERT 0 1




id
jdata


1
{"Info": [{"coordinate": {"latitude": 70, "longitude": -150}, "timestampPos": "2022-06-30T01:00:00"}, {"coordinate": {"latitude": 65, "longitude": -140}, "timestampPos": "2022-06-30T02:00:00"}, {"coordinate": {"latitude": 60, "longitude": -135}, "timestampPos": "2022-06-30T03:00:00"}, {"coordinate": {"latitude": 55, "longitude": -130}, "timestampPos": "2022-06-30T04:00:00"}], "header": {"Id": "xxx", "hardwareId": 0, "timestampMsg": "xxx", "manufacturerId": ""}}



SELECT 1


select t.id,
       (e.el->>'timestampPos')::timestamp as timestamppos,
       (e.el->'coordinate'->>'latitude')::float8 as latitude,
       (e.el->'coordinate'->>'longitude')::float8 as longitude
  from a_table t
       cross join lateral jsonb_array_elements(t.jdata->'Info') e(el);


id
timestamppos
latitude
longitude


1
2022-06-30 01:00:00
70
-150

1
2022-06-30 02:00:00
65
-140

1
2022-06-30 03:00:00
60
-135

1
2022-06-30 04:00:00
55
-130



SELECT 4

id	timestamppos	latitude	longitude
1	2022-06-30 01:00:00	70	-150
1	2022-06-30 02:00:00	65	-140
1	2022-06-30 03:00:00	60	-135
1	2022-06-30 04:00:00	55	-130