PostgreSQL json extraction

Question

I have json data about flight reservations coming from a MongoDB. This data is inseted into a PostgreSQL table and the data type is json. The itinerary information has the following structure.

[
  {
    "segments": {
      "1": {
        "airline": "G8",
        "flight_number": "761",
        "arrival-date": "2018-11-02T12:45:00",
        "arrival_airport": "CCU",
        "departure_date": "2018-11-02T11:35:00",
        "departure_airport": "BBI",
        "operating_airline": "G8",
        "stops": 0,
        "index": 1,
        "duration": 4200
      }
    }
  },
  {
    "segments": {
      "2": {
        "airline": "6E",
        "flight_number": "775",
        "arrival-date": "2018-11-03T00:30:00",
        "arrival_airport": "BBI",
        "departure_date": "2018-11-02T23:20:00",
        "departure_airport": "CCU",
        "operating_airline": "6E",
        "stops": 0,
        "index": 2,
        "duration": 4200
      }
    }
  }
]

The number of segments is unknown. How can this be split into multiple records so that each segment becomes a record? json_array_elements is a function I am already using and if I use json_array_elements(data->'flights')->'segments', I get output like this

{
  "1": {
    "airline": "AI",
    "flight_number": "20",
    "arrival_airport": "CCU",
    "arrival-date": "2018-10-17T16:40:00",
    "departure_date": "2018-10-17T14:25:00",
    "departure_airport": "DEL",
    "operating_airline": "AI",
    "stops": 0,
    "index": 1,
    "duration": 8100
  },
  "2": {
    "airline": "AI",
    "flight_number": "230",
    "arrival_airport": "DAC",
    "arrival-date": "2018-10-17T20:25:00",
    "departure_date": "2018-10-17T19:00:00",
    "departure_airport": "CCU",
    "operating_airline": "AI",
    "stops": 0,
    "index": 2,
    "duration": 3300
  }
}

How can this be converted into individual records? I would like to execute an insert like this -

insert into stage_itinerary (segment_id,airline,flight_number,arrival_aiport...)  select (....)

S-Man · Accepted Answer · 2019-12-10 14:32:53Z

2

Click: demo:db<>fiddle

You can use json_array_elements():

SELECT
    elems
FROM 
    mytable,
    json_array_elements(my_segments_column) elems

If you want to update your table with the split data, you need to insert the new data and remove the old one. This can be done in one query using a CTE (WITH clause):

Click: demo:db<>fiddle

WITH del AS (
    DELETE FROM mytable
    RETURNING my_segments_column
)
INSERT INTO mytable
SELECT
    elems
FROM 
    del,
    json_array_elements(my_segments_column) elems;

Within the CTE, all data is removed, but returned through the RETURNING clause. This returned data is used do calculate the split records as shown above, which will be inserted afterwards.

edited Dec 10, 2019 at 14:32

answered Dec 10, 2019 at 14:26

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Comment

Jayadevan Over a year ago

Apologies for not making the problem clear. I have edited my question.

Haleemur Ali · Accepted Answer · 2019-12-11 05:05:19Z

This is actually a very good use case for stacking multiple lateral queries, because each lateral query can refer to all the objects defined before it.

SELECT a4.*
FROM test
, LATERAL (SELECT a1->'segments' a2 FROM jsonb_array_elements(a) a1) a1
, LATERAL (SELECT value FROM jsonb_each(a2)) a3                    
, LATERAL (SELECT * 
           FROM jsonb_to_record(a3) a4 
           ("index" int, stops int, airline text, duration int, 
            "arrival-date" timestamptz, flight_number text, departure_date text,
            arrival_airport text, departure_airport text, operating_airline text)
          ) a4

If you are lucky enough to be using postgresql 12 or higher, then the above query can be written more elegantly as:

SELECT a2.*
FROM test
, LATERAL (SELECT * FROM jsonb_path_query(a, '$.*.*') a1 ) a1
, LATERAL (SELECT * 
           FROM jsonb_to_record(a1) a2 
          ("index" int, stops int, airline text, duration int, 
           "arrival-date" timestamptz, flight_number text, departure_date text,
           arrival_airport text, departure_airport text, operating_airline text)
) a2

Here is the setup code:

CREATE TABLE test (a) AS SELECT 
'[
  {
    "segments": {
      "1": {
        "airline": "G8",
        "flight_number": "761",
        "arrival-date": "2018-11-02T12:45:00",
        "arrival_airport": "CCU",
        "departure_date": "2018-11-02T11:35:00",
        "departure_airport": "BBI",
        "operating_airline": "G8",
        "stops": 0,
        "index": 1,
        "duration": 4200
      }
    }
  },
  {
    "segments": {
      "2": {
        "airline": "6E",
        "flight_number": "775",
        "arrival-date": "2018-11-03T00:30:00",
        "arrival_airport": "BBI",
        "departure_date": "2018-11-02T23:20:00",
        "departure_airport": "CCU",
        "operating_airline": "6E",
        "stops": 0,
        "index": 2,
        "duration": 4200
      }
    }
  }
]'::jsonb

Collectives™ on Stack Overflow

PostgreSQL json extraction

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related