Question
query JSON in posgreSQL
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