Replacing a string on whole database

&amp char has somehow got through different imports into the db on many different node attributes and relationship attributes. How do I replace all & strings with regular & char?

I don't know all the possible property names that I can filter on.

Solution

If you want to make this efficient, you can use CALL{} in transactions of X rows

The :auto prefix is needed if you want to run this query in the Neo4j browser

This line

WITH n, [x in keys(n) WHERE n[x] CONTAINS '&amp'] AS keys

is needed to avoid trying a replace function on a property that is not of String type, in which case Neo4j will throw an exception.

Full query

:auto MATCH (n)
CALL {
    WITH n
    WITH n, [x in keys(n) WHERE n[x] CONTAINS '&amp'] AS keys
    CALL apoc.create.setProperties(n, keys, [k in keys | replace(n[k], '&amp', '&')])
    YIELD node
    RETURN node
} IN TRANSACTIONS OF 100 ROWS
RETURN count(*)

If you're using a Neo4j cluster, you will need to run this on the leader of the database with the bolt connection ( not using the neo4j:// protocol.

Same query for the relationships now

:auto MATCH (n)-[r]->(x)
CALL {
    WITH r
    WITH r, [x in keys(r) WHERE r[x] CONTAINS '&amp'] AS keys
    CALL apoc.create.setRelProperties(r, keys, [k in keys | replace(r[k], '&amp', '&')])
    YIELD rel
    RETURN rel
} IN TRANSACTIONS OF 100 ROWS
RETURN count(*)