Get data from nested object

SQL++
,
1

I want to get the only id from ‘element’ field along with other fields in select, I am trying unnest but it does not work.

My query:

select i.validationLogicId, i.businessValidationId, i.`element`, i.severity, e.id from businessValidation i
unnest i.`element` as e
and i.status !='cleared'

{
    "businessValidationId": "missingMandatoryFieldValidation",
    "element": {
      "id": "d9c9bab2-3d6f-44cd-99c4-0f63a09da2ac",
      "indexType": "productOffering",
      "type": "productOffering"
    },
    "severity": "error",
    "validationLogicId": "CAT1POTYPE100052"
  }
2

@akibandali ,

UNNEST is self join of actual document with array element

For object use dot field i.e. i.element.id

SELECT i.validationLogicId, i.businessValidationId, i.`element`.id, i.severity
FROM  businessValidation  AS i
1 Like
3

Yes you are right , Thanks