Query "Last updated" from nested entity?

SQL++
5

There is typo Updates and TO_ARRAY needs to be outside

 SELECT d.*
FROM data AS d
UNNEST TO_ARRAY(IFMISSINGORNULL(d.RevisionInfo.Updates,{"Modified":d.RevisionInfo.Created, "ModifiedBy":d.RevisionInfo.CreatedBy})) AS du
WHERE d.Name LIKE "%Doe%"
ORDER BY du.Modified DESC;

There are 2 books Special Edition applicable only 5.5, second edition applicable 5.0, 5.5 ,…

if you don’t give name to primary index it uses #primary

If you are expecting latest updated document across result set
(For further optimization and explanation check Select non-group by fields using sql query)

SELECT RAW MAX([(SELECT RAW MAX(du.Modified) FROM IFMISSINGORNULL(d.RevisionInfo.Updates,{"Modified":d.RevisionInfo.Created, "ModifiedBy":d.RevisionInfo.CreatedBy}) AS du)[0],d])[1]
FROM data AS d
WHERE d.Name LIKE "%Doe%";