Having thought this through some more, I'll probably just create a struct field for each data type. I can use from_json to create the struct field.

  select HOLEID,
        PROJECTCODE,
        from_json(vf_string, 'MAP<STRING, STRING>') as vf_string
  from
    (
        SELECT HOLEID,
              PROJECTCODE,
              concat('{', concat_ws(',', collect_list(concat('"',a.NAME,'":"',a.VALUE,'"'))),'}') as vf_string
        FROM rtx.raw_acq_aar.holedetails a 
        join rtx.raw_acq_aar.holecode b
        on a.NAME = b.NAME
        where b.DATATYPE = 'Text'
        GROUP BY HOLEID, PROJECTCODE
    ) a