UPDATE Query with missing or null value in path

If I run an update query, similar to the following:

update fsm
USE KEYS "unique-key-of-the-single-document"
set `hvacr_equipment`.`fs_geo_pt_location`.`testing`="testing123" for `hvacr_equipment` in `hvacr_equipment` when `hvacr_equipment`.`_id`="some-unique-id-value" end
where doc_type = "hvacr_system"

When hvacr_equipment.fs_geo_pt_location is not in the document OR has a value set to null, the query does not update it.

If, however, hvacr_equipment.fs_geo_pt_location is set, it is updated properly.

How do I get this query to create hvacr_equipment.fs_geo_pt_location if it doesn’t exist and then set the testing value in it?

Same question if it does exist and it’s set to null?

Thanks

UPDATE fsm
USE KEYS "unique-key-of-the-single-document"
SET
    he.fs_geo_pt_location= {}
         FOR he IN `hvacr_equipment` WHEN he.`_id` = "some-unique-id-value" AND he.fs_geo_pt_location IS NOT VALUED END,
    he.fs_geo_pt_location.testing = "testing123"
         FOR he IN `hvacr_equipment` WHEN he.`_id` = "some-unique-id-value" END,
WHERE doc_type = "hvacr_system"

If fs_geo_pt_location is not a object you will have same problem. If you want you can change he.fs_geo_pt_location IS NOT VALUED to IS_OBJECT(he.fs_geo_pt_location) = false

1 Like