API

Other

Description

We have a function called regexp_replace that replaces only the first match of a string based on a regex.

However, we should also provide an option to replace all occurrences of a match, via a function regexp_replace_all. Example below.

I have this JSON file persons.json:

[
  {
    "name": "Xavier Williams",
    "date_of_birth": "1989-06-28",
    "residence": {
      "address": "901 Oak Lane",
      "city": "San Francisco",
      "state": "CA",
      "zip_code": "94122"
    }
  }
]

I want to generate a unique string primary key that's a concatenation of all the address fields, that I can then use for relationship creation downstream. During this process, I want to remove all whitespaces.

I'm doing this right now:

LOAD FROM 'persons.json'
WITH DISTINCT residence AS r
RETURN
    concat(
        regexp_replace(lower(r.address), ' ', ''),
        regexp_replace(lower(r.city), ' ', ''),
        regexp_replace(lower(r.state), ' ', ''),
        r.zip_code
    ) AS id,
    r.address,
    r.city,
    r.state,
    r.zip_code

Because only the first occurrence is matched, I get this result for the id field:

901oak lanesanfranciscoca94122

There's an extra space in the middle that wasn't removed.

If we had a regexp_replace_all function, this would be resolved.