I’m attempting to update a set of related documents. One of the documents has an array of values I was expecting in my other documents, but it was not generated properly. Instead of regenerating my entire dataset, I want to correct it. In traditional SQL I would do this with a CTE or a subselect join on the related key and set the new value. But I cannot find anything in searching this forum that makes mention to that kinda of feature. By “related” I mean that a “placeId” can have 13 languages. In english, the array is present, in every other language it is null. So I would iterate over each placeId and replace the null with the english value. Here’s an example of the pseudo SQL I would write if it was MSSQL:
update d set d.amenities = a.amenities from documents d join ( select amenities, placeid from documents where language = 'english' ) a on d.placeid = a.placeid where d.language <> 'english'