Query complex JSON in Postgres

Question

I have a json column named "Payload" with a complex json structure as below-

{
    "Number": "",
    "Status": "",
    "Parties": [
        {
            "BeCode": "SHANGMAG",
            "PartyCode": "CNSHANGMAGVDR",
        },
        {
            "BeCode": "FREEMAN",
            "PartyCode": "CNFREEMANVDR",
        }
    ],
    "ContactName": "test",
    "Type": "",
    "Legs": [
        {
            "Name": "",
            "ELocation": {
                "City": "Enns",
                "State": null,
                "Country": "Austria",
            },
            "Socation": {
                "City": "Buenos Aires",
                "State": null,
                "Country": "Argentina",
            },
            "Transport": 1
        },
        {
            "Name": "84nbt",
            "ELocation": {
                "City": "Linz",
                "State": null,
                "Country": "Austria",
            },
            "SLocation": {
                "City": "Enns",
                "State": null,
                "Country": "Austria",
            },
            "Transport": 2
        }
    ]
    "Bookings": [
        {
            "BookingNo": "",
            "Status": "",
            "Id": ""
        }
    ]
}

Now I need to query all the rows where SLocation is equal to ELocation.

I was able to get the "Legs" part row vise using following query -

select payload->'Legs' 
from public.shipping_instruction

However, If I dig deep into the json to get the SLocation and ELocation, the query doesnt exceute.

I am looking for something like the one below-

select payload->'Legs' 
from public.shipping_instruction where
payload->'Legs'->'ELocation'->'City' =
payload->'Legs'->'SLocation'->'City' 

But then here the Legs have multiple SLocation and ELocation how do I handle it?

Answer 1

select "Number",
       x."Status" as Status,
       "BeCode",
       "PartyCode",
       "ContactName",
       "Type",
       "Name",
       "Transport",
       e_city,
       e_state,
       e_country,
       s_city,
       s_state,
       s_country,
       "BookingNo",
       b."Status" as BookingStatus,
       "Id"
from jsonb_to_record('{
  "Number": "",
  "Status": "",
  "Parties": [
    {
      "BeCode": "SHANGMAG",
      "PartyCode": "CNSHANGMAGVDR"
    },
    {
      "BeCode": "FREEMAN",
      "PartyCode": "CNFREEMANVDR"
    }
  ],
  "ContactName": "test",
  "Type": "",
  "Legs": [
    {
      "Name": "",
      "ELocation": {
        "City": "Enns",
        "State": null,
        "Country": "Austria"
      },
      "SLocation": {
        "City": "Buenos Aires",
        "State": null,
        "Country": "Argentina"
      },
      "Transport": 1
    },
    {
      "Name": "84nbtMatch",
      "ELocation": {
        "City": "Linz",
        "State": null,
        "Country": "Austria"
      },
      "SLocation": {
        "City": "Linz",
        "State": null,
        "Country": "Austria"
      },
      "Transport": 2
    },
    {
      "Name": "84nbt",
      "ELocation": {
        "City": "Linz",
        "State": null,
        "Country": "Austria"
      },
      "SLocation": {
        "City": "Enns",
        "State": null,
        "Country": "Austria"
      },
      "Transport": 3
    }
  ],
  "Bookings": [
    {
      "BookingNo": "bn",
      "Status": "bs",
      "Id": "bid"
    }
  ]
}'::jsonb) as x("Number" text,
                "Status" text,
                "Parties" jsonb,
                "ContactName" text,
                "Type" text,
                "Legs" jsonb,
                "Bookings" jsonb),
     lateral jsonb_to_recordset(x."Parties") as p("BeCode" text, "PartyCode" text),
     lateral jsonb_to_recordset(x."Legs") as l("Name" text, "Transport" int, "ELocation" jsonb, "SLocation" jsonb),
     lateral (select l."ELocation" ->> 'City'    as e_city,
                     l."ELocation" ->> 'State'   as e_state,
                     l."ELocation" ->> 'Country' as e_country,
                     l."SLocation" ->> 'City'    as s_city,
                     l."SLocation" ->> 'State'   as s_state,
                     l."SLocation" ->> 'Country' as s_country
         ) loc,
     lateral jsonb_to_recordset(x."Bookings") as b("BookingNo" text, "Status" text, "Id" text)
where coalesce(e_city, '') = coalesce(s_city, '')
  and coalesce(e_state, '') = coalesce(s_state, '')
  and coalesce(e_country, '') = coalesce(s_country, '');