Use variable from parent query in correlated query

SQL++
2

Your subquery is correlated due to pin_id IN [META§.id]. Correlated subquery requires USE KEYS clause. In your case in parent query predicate META§.id is equality predicate you can try this.

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,
       (SELECT COUNT(1) AS count,`pin_id`
           FROM events USE INDEX(`like` USING GSI)
           WHERE `pin_id` IN ['a5d3c6c4-9815-4b41-ba09-82ce0699d839'] AND type="like" AND status=1
           GROUP BY pin_id) AS `like_count`
FROM pins p USE INDEX(`#pins` USING GSI)
INNER JOIN users AS `u` ON KEYS `p`.user_id
WHERE META(p).id='a5d3c6c4-9815-4b41-ba09-82ce0699d839'
AND p.pin_type='report';