Use variable from parent query in correlated query

SQL++
4

If pin_id of type = “like”, pin_type= “report” same replace that.

If not
change your data mode store the document id of type = “like” in other document

OR

Do non correlated query and apply array collection.

SELECT p.place_name,p.event_id,p.place_id,p.pin_story,p.place_lat,p.place_long,p.cat_name,
       p.child_cat_name,p.broadcast,p.status, p.pin_create_date,
       ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.images END AS pictures,
       ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.videos END AS videos,
       'http://development.mapmyindia.com/api/v2.0.0/report/'||META(p).id AS resourceLocation,p.user_id,u.name,u.user_photo,META(p).id AS _ID,
       META(p).cas AS _CAS,
       FIRST v.`cnt` FOR v IN lk WHEN v.pin_id = META(p).id END AS `like_count`
FROM pins p USE INDEX(`#pins` USING GSI)
LET lk = (SELECT COUNT(1) AS cnt,`pin_id`
           FROM events USE INDEX(`like` USING GSI)
           WHERE type="like" AND status=1
           GROUP BY pin_id)
INNER JOIN users AS `u` ON KEYS `p`.user_id
WHERE META(p).id='a5d3c6c4-9815-4b41-ba09-82ce0699d839'
AND p.pin_type='report';