In PostgreSQL, filter with "IN" and "Order By" when there is a array in side of a JSON values

Question

Lets assume table has below mentions fields and among those fields, "details" column keeps values in JSON format.

| id | firstName | lastName | title | details(json) |

example for json that keeps in details column can be like this

{
  "email": "example@gmail.com",
  "phoneNumber1": "+94111111111",
  "phoneNumber2": "+44111111111",
  "locations": [
    {
      "code": "LK",
      "name": "Sri Lanka",
      "lat": 128.12,
      "lon": 138.23
    },
    {
      "code": "UK",
      "name": "England",
      "lat": 148.12,
      "lon": 158.23
    },
    {
      "code": "IND",
      "name": "India",
      "lat": 163.12,
      "lon": 172.23
    }
  ]
}

I need to filter from locations code and same time need to order by details email.

As an example,

--------------------------------
details->>'code' IN ('LK','UK') 
ORDER BY details->>'email'
--------------------------------

PostgreSQL 13.4

Answer 1

I have solved this one using below mentioned query

The query,

SELECT firstname, lastname, details 
FROM
(
SELECT DISTINCT((person.details)::jsonb) as details, person.firstname, 
person.lastname
FROM person 
CROSS JOIN json_array_elements(person.details->'locations') AS locations
WHERE locations->>'code' IN ('LK','GB')
) as result
ORDER BY details->'email' DESC

Because, The ORDER BY clause can only be applied after the DISTINCT has been applied