Replacing field name prefix

Hi N1QL gurus!

Let’s say you have these two documents:

{
  "__foo_name" : "Alice",
  "__foo_number" : 1,
  "magicWord" : "xyzzy"
}

{
  "__foo_pet" : "cat"
  "__foo_music" : "rock"
}

The prefix __foo_ is known, but the suffixes are not known in advance. Without knowing the existing field names, can N1QL rename all the fields whose name starts with __foo_, replacing that prefix with something else?

Desired document state after the update would be:

{
  "bar$name" : "Alice",
  "bar$number" : 1,
  "magicWord" : "xyzzy"
}

{
  "bar$pet" : "cat"
  "bar$music" : "rock"
}

Thanks,
David

One level. Nested levels makes complex or not possible. You are looking fixed prefix you can create even index and filter them out.

INSERT INTO default VALUES ("f01", { "__foo_name" : "Alice", "__foo_number" : 1, "magicWord" : "xyzzy" }),
                    VALUES ("f02", { "_afoo_pet" : "cat", "__foo_music" : "rock" });
SELECT  RAW  OBJECT  (CASE WHEN SUBSTR(v.name,0, length("__foo_")) == "__foo_"  THEN CONCAT("bar$", SUBSTR(v.name,length("__foo_"))) ELSE v.name END):v.val FOR v IN  OBJECT_PAIRS(d) END
FROM default AS d;

UPDATE default AS d
SET d = OBJECT  (CASE WHEN SUBSTR(v.name,0, length("__foo_")) == "__foo_"  THEN CONCAT("bar$", SUBSTR(v.name,length("__foo_"))) ELSE v.name END):v.val FOR v IN  OBJECT_PAIRS(d) END;

With index

SELECT  RAW  OBJECT  (CASE WHEN SUBSTR(v.name,0, length("__foo_")) == "__foo_"  THEN CONCAT("bar$", SUBSTR(v.name,length("__foo_"))) ELSE v.name END):v.val FOR v IN  OBJECT_PAIRS(d) END
FROM default AS d
WHERE ANY v IN OBJECT_NAMES(d) SATISFIES SUBSTR(v, 0 , length("__foo_")) == "__foo_" END AND META(d).id > "";

UPDATE default AS d
SET d = OBJECT  (CASE WHEN SUBSTR(v.name,0, length("__foo_")) == "__foo_"  THEN CONCAT("bar$", SUBSTR(v.name,length("__foo_"))) ELSE v.name END):v.val FOR v IN  OBJECT_PAIRS(d) END
WHERE ANY v IN OBJECT_NAMES(d) SATISFIES SUBSTR(v, 0 , length("__foo_")) == "__foo_" END AND META(d).id > "";

CREATE INDEX ix1 ON default(META(self).id) WHERE ANY v IN OBJECT_NAMES(self) SATISFIES SUBSTR(v, 0 , length("__foo_")) == "__foo_" END;
1 Like