How to convert a JSON to table in PostgreSQL-CodePudding

I have a table where in a column there are some JSONs with this form:

{
  "header": {
    "hardwareId": 0,
    "Id": "xxx",
    "manufacturerId": "",
    "timestampMsg": "xxx"
  },

  "Info": [{
    "timestampPos": "xxx",
    "coordinate": {
      "latitude": xxx,
      "longitude": xxxx
    },

  {
    "timestampPos": "xxx",
    "coordinate": {
      "latitude": xxx,
      "longitude": xxxx
    },
    
 {
    "timestampPos": "xxx",
    "coordinate": {
      "latitude": xxx,
      "longitude": xxxx
    },

 {
    "timestampPos": "xxx",
    "coordinate": {
      "latitude": xxx,
      "longitude": xxxx
  }
 }]
}

I need to exctract a table with three columns: Timestamp, Lat, and Lon, for any JSON. How can i do it?

Thanks.

CodePudding user response：

You can use a cross join with jsonb_array_elements:

select t.js -> 'header' -> 'Id' id, 
       v.value -> 'timestampPos' tStamp, 
       v.value -> 'coordinate' -> 'latitude' lat, 
       v.value -> 'coordinate' -> 'longitude' long 
from tbl t cross join jsonb_array_elements(t.js -> 'Info') v

CodePudding user response：

So long as your json is valid (not like the example in your question), you can use cross join lateral to jsonb_array_elements(), and then dereference and cast the values:

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);

db<>fiddle here