query JSON in posgreSQL

[DavidKangni]

Hi there, 

I need help to get the data from a JSON object using SQL in AJO. 

{
  "consent": {
    "idSpecific": {
      "Phone": {
        "75708": {
          "marketing": {
            "sms": {
              "val": "y",
              "time": "2023-12-06T17:22:46.118Z"
            }
          }
        }
      }
    }
  }
}

I tried the following code but it's not returning the value (I'm able to parse identityMap tho)

select a.identityMap['clientID'][0].ID AS cardUnmasked, TIMESTAMP, b.consents.marketing.sms.val, explode(b.consents.idSpecific),
to_json(b.consents.idSpecific)
from ajo_message_feedback_event_dataset a
inner join profile_snapshot_export_987e0af1_adb6_45b0_9a58_4565707ef05d b
on a.identityMap['clientID'][0].ID = b._client.identities.cardUnmasked
where
     a._experience.customerJourneyManagement.messageExecution.journeyVersionID IN ('1ffd0d80-d2e7-4d75-a097-e5e5e54f19a6')
     and b._riteaid.identities.cardUnmasked = '95087080686'

Thanks,

David