Subnesting from the document

SQL++
1

Hi ,

I need to make the query for the inner nesting. Require help for the query.

  "Roles": [
      {
        "emailContacts": [
          {
            "contactMediumId": 35266,
            "contactMediumSpecId": -4,
            "emailAddress": "ABC@YMAIL.COM",
            "priorityOrder": 1
          }
        ],
        "RoleId": "10273",
        "relevantEntityId": "16732", -- Two
        "roleSpecId": -1,
        "statusLastChangedDate": "2020-08-01T00:00:00.000Z",
        "telephoneContacts": [
          {
            "contactMediumId": 35267,
            "contactMediumSpecId": -3,
            "phoneNumber": "123456",
            "phoneType": 1,
            "priorityOrder": 1
          },
          {
            "contactMediumId": 35269,
            "contactMediumSpecId": -3,
            "phoneNumber": "1234587",
            "phoneType": 1,
            "priorityOrder": 2
          },
          {
            "contactMediumId": 35270,
            "contactMediumSpecId": -3,
            "phoneNumber": "123487",
            "phoneType": 1,
            "priorityOrder": 3
          },
          {
            "contactMediumId": 35272,
            "contactMediumSpecId": -2,
            "phoneNumber": "1569971",
            "phoneType": 1,
            "priorityOrder": 4
          }
          ]
      }
    ]

Name of the bucket is employee.

Expected output.

Roles.relevantEntityId : 16732,
Roles. roleSpecId, : -1,
contactMediumId : 35237,
phoneNumber : 123456
priorityOrder : 1

Roles.relevantEntityId : 16732,
Roles. roleSpecId, : -1,
contactMediumId : 35269,
phoneNumber : 1234587
priorityOrder : 2

Roles.relevantEntityId : 16732,
Roles. roleSpecId, : -1,
contactMediumId : 35270,
phoneNumber : 123487
priorityOrder : 3

Could you help to write the query.

2 
SELECT r.relevantEntityId, r.roleSpecId, tc.contactMediumId, tc.phoneNumber, tc.priorityOrder
FROM default AS d 
UNNEST d.Roles AS r
UNNEST r.telephoneContacts AS tc
WHERE ......
ORDER BY tc.priorityOrder;
3

Hi, Thank you very much for the query. I am able to extract the data.