How to edit JSON file to remove particular client entries with jq or sed or similar command

Question

Having a json file menu.json and need to remove only client2 related entries from the "collect.whitelist" line. Any help appreciated.

{
    "connect.class": "io.connector.MongoDbConnect",
    "tasks.max": "2",
    "snapshot.delay.ms": "1500",
    "collect.whitelist": "client1_creat.plan.header,client1_creat.plan.ref-data,client1_creat.plan.line.view,client1_creat.plan.inventory.view,client1_creat.plan.period.lines,client1_creat.plan.products,client1_creat.constraint,client1_creat.separation,client1_creat.plan.guidelines,client1_creat.plan.spots,client1_creat.plan.spot.links,client1_creat.plan.deal.link,client1_creat.plan.line.target,client1_creat.plan.digital.period.lines,client1_creat.plan.digital.lines,client1_creat.plan.deal.link,client1_creat.plan.quarter.view,client1_creat.plan.line.weekly.spots,client1_creat.plan.total.view,client1_creat.plan.weekly.view,client1_creat.condition.status,client1_creat.plan.additionalfees,client1_creat.plan.digital.period.view,client1_creat.plan.line.weekly.totals.view,client1_creat.plan.buyer.goals,client1_creat.plan.period.line.lineage.comments,client1_creat.plan.books,client1_creat.plan.line.attributes,client2_creat.plan.header,client2_creat.plan.ref-data,client2_creat.plan.line.view,client2_creat.plan.inventory.view,client2_creat.plan.period.lines,client2_creat.plan.products,client2_creat.constraint,client2_creat.separation,client2_creat.plan.guidelines,client2_creat.plan.spots,client2_creat.plan.spot.links,client2_creat.plan.deal.link,client2_creat.plan.line.target,client2_creat.plan.digital.period.lines,client2_creat.plan.digital.lines,client2_creat.plan.deal.link,client2_creat.plan.quarter.view,client2_creat.plan.line.weekly.spots,client2_creat.plan.total.view,client2_creat.plan.weekly.view,client2_creat.condition.status,client2_creat.plan.additionalfees,client2_creat.plan.digital.period.view,client2_creat.plan.line.weekly.totals.view,client2_creat.plan.buyer.goals,client2_creat.plan.period.line.lineage.comments,client2_creat.plan.books,client2_creat.plan.line.attributes,client3_creat.plan.header,client3_creat.plan.ref-data,client3_creat.plan.line.view,client3_creat.plan.inventory.view,client3_creat.plan.period.lines,client3_creat.plan.products,client3_creat.constraint,client3_creat.separation,client3_creat.plan.guidelines,client3_creat.plan.spots,client3_creat.plan.spot.links,client3_creat.plan.deal.link,client3_creat.plan.line.target,client3_creat.plan.digital.period.lines,client3_creat.plan.digital.lines,client3_creat.plan.deal.link,client3_creat.plan.quarter.view,client3_creat.plan.line.weekly.spots,client3_creat.plan.total.view,client3_creat.plan.weekly.view,client3_creat.condition.status,client3_creat.plan.additionalfees,client3_creat.plan.digital.period.view,client3_creat.plan.line.weekly.totals.view,client3_creat.plan.buyer.goals,client3_creat.plan.period.line.lineage.comments,client3_creat.plan.books,client3_creat.plan.line.attributes,client4_creat.plan.header,client4_creat.plan.ref-data,client4_creat.plan.line.view,client4_creat.plan.inventory.view,client4_creat.plan.period.lines,client4_creat.plan.products,client4_creat.constraint,client4_creat.separation,client4_creat.plan.guidelines,client4_creat.plan.spots,client4_creat.plan.spot.links,client4_creat.plan.deal.link,client4_creat.plan.line.target,client4_creat.plan.digital.period.lines,client4_creat.plan.digital.lines,client4_creat.plan.deal.link,client4_creat.plan.quarter.view,client4_creat.plan.line.weekly.spots,client4_creat.plan.total.view,client4_creat.plan.weekly.view,client4_creat.condition.status,client4_creat.plan.additionalfees,client4_creat.plan.digital.period.view,client4_creat.plan.line.weekly.totals.view,client4_creat.plan.buyer.goals,client4_creat.plan.period.line.lineage.comments,client4_creat.plan.books,client4_creat.plan.line.attributes",
    "ssl.enabled": "true",
    "members.auto.discover": "true",
    "name": "source_mongodb",
    "ssl.invalid.allowed": "true",
    "snapshot.mode": "never"
}

Tried different jq and sed option but can't help. ex:

jq '.[] | select(.collect.whitelist|test("^client2."))' menu.json

or

sed -e 's/^client2//g' menu.json

Answer 1

Array-based solution

Use ."collect.whitelist" (with quotes) to make the dot part of the name, and update the field using the update operator |=. Therein, split the string at commas into an array using /, iterate and manipulate the items using map and select, while using startswith to identify the relevant items, and not to negate, then re-join the array with commas using join:

jq '."collect.whitelist" |= (. / "," | map(select(startswith("client2_") | not)) | join(","))' menu.json

{
  "connect.class": "io.connector.MongoDbConnect",
  "tasks.max": "2",
  "snapshot.delay.ms": "1500",
  "collect.whitelist": "client1_creat.plan.header,client1_creat.plan.ref-data,client1_creat.plan.line.view,client1_creat.plan.inventory.view,client1_creat.plan.period.lines,client1_creat.plan.products,client1_creat.constraint,client1_creat.separation,client1_creat.plan.guidelines,client1_creat.plan.spots,client1_creat.plan.spot.links,client1_creat.plan.deal.link,client1_creat.plan.line.target,client1_creat.plan.digital.period.lines,client1_creat.plan.digital.lines,client1_creat.plan.deal.link,client1_creat.plan.quarter.view,client1_creat.plan.line.weekly.spots,client1_creat.plan.total.view,client1_creat.plan.weekly.view,client1_creat.condition.status,client1_creat.plan.additionalfees,client1_creat.plan.digital.period.view,client1_creat.plan.line.weekly.totals.view,client1_creat.plan.buyer.goals,client1_creat.plan.period.line.lineage.comments,client1_creat.plan.books,client1_creat.plan.line.attributes,client3_creat.plan.header,client3_creat.plan.ref-data,client3_creat.plan.line.view,client3_creat.plan.inventory.view,client3_creat.plan.period.lines,client3_creat.plan.products,client3_creat.constraint,client3_creat.separation,client3_creat.plan.guidelines,client3_creat.plan.spots,client3_creat.plan.spot.links,client3_creat.plan.deal.link,client3_creat.plan.line.target,client3_creat.plan.digital.period.lines,client3_creat.plan.digital.lines,client3_creat.plan.deal.link,client3_creat.plan.quarter.view,client3_creat.plan.line.weekly.spots,client3_creat.plan.total.view,client3_creat.plan.weekly.view,client3_creat.condition.status,client3_creat.plan.additionalfees,client3_creat.plan.digital.period.view,client3_creat.plan.line.weekly.totals.view,client3_creat.plan.buyer.goals,client3_creat.plan.period.line.lineage.comments,client3_creat.plan.books,client3_creat.plan.line.attributes,client4_creat.plan.header,client4_creat.plan.ref-data,client4_creat.plan.line.view,client4_creat.plan.inventory.view,client4_creat.plan.period.lines,client4_creat.plan.products,client4_creat.constraint,client4_creat.separation,client4_creat.plan.guidelines,client4_creat.plan.spots,client4_creat.plan.spot.links,client4_creat.plan.deal.link,client4_creat.plan.line.target,client4_creat.plan.digital.period.lines,client4_creat.plan.digital.lines,client4_creat.plan.deal.link,client4_creat.plan.quarter.view,client4_creat.plan.line.weekly.spots,client4_creat.plan.total.view,client4_creat.plan.weekly.view,client4_creat.condition.status,client4_creat.plan.additionalfees,client4_creat.plan.digital.period.view,client4_creat.plan.line.weekly.totals.view,client4_creat.plan.buyer.goals,client4_creat.plan.period.line.lineage.comments,client4_creat.plan.books,client4_creat.plan.line.attributes",
  "ssl.enabled": "true",
  "members.auto.discover": "true",
  "name": "source_mongodb",
  "ssl.invalid.allowed": "true",
  "snapshot.mode": "never"
}

Demo

Regex-based solution

You can also update the string directly (without splitting and joining) using gsub, first to replace any occurrence between commas, or at the start, or at the end of the string, with just a comma, then to replace all consecutive commas with just one comma. Finally, remove leading or trailing commas using ltrimstr and rtrimstr (present if the deleted item was the first or last item):

jq '."collect.whitelist" |= (gsub("((^|,)client2_[^,]*(,|$))"; ",") | gsub(",+"; ",") | ltrimstr(",") | rtrimstr(","))' menu.json

{
  "connect.class": "io.connector.MongoDbConnect",
  "tasks.max": "2",
  "snapshot.delay.ms": "1500",
  "collect.whitelist": "client1_creat.plan.header,client1_creat.plan.ref-data,client1_creat.plan.line.view,client1_creat.plan.inventory.view,client1_creat.plan.period.lines,client1_creat.plan.products,client1_creat.constraint,client1_creat.separation,client1_creat.plan.guidelines,client1_creat.plan.spots,client1_creat.plan.spot.links,client1_creat.plan.deal.link,client1_creat.plan.line.target,client1_creat.plan.digital.period.lines,client1_creat.plan.digital.lines,client1_creat.plan.deal.link,client1_creat.plan.quarter.view,client1_creat.plan.line.weekly.spots,client1_creat.plan.total.view,client1_creat.plan.weekly.view,client1_creat.condition.status,client1_creat.plan.additionalfees,client1_creat.plan.digital.period.view,client1_creat.plan.line.weekly.totals.view,client1_creat.plan.buyer.goals,client1_creat.plan.period.line.lineage.comments,client1_creat.plan.books,client1_creat.plan.line.attributes,client3_creat.plan.header,client3_creat.plan.ref-data,client3_creat.plan.line.view,client3_creat.plan.inventory.view,client3_creat.plan.period.lines,client3_creat.plan.products,client3_creat.constraint,client3_creat.separation,client3_creat.plan.guidelines,client3_creat.plan.spots,client3_creat.plan.spot.links,client3_creat.plan.deal.link,client3_creat.plan.line.target,client3_creat.plan.digital.period.lines,client3_creat.plan.digital.lines,client3_creat.plan.deal.link,client3_creat.plan.quarter.view,client3_creat.plan.line.weekly.spots,client3_creat.plan.total.view,client3_creat.plan.weekly.view,client3_creat.condition.status,client3_creat.plan.additionalfees,client3_creat.plan.digital.period.view,client3_creat.plan.line.weekly.totals.view,client3_creat.plan.buyer.goals,client3_creat.plan.period.line.lineage.comments,client3_creat.plan.books,client3_creat.plan.line.attributes,client4_creat.plan.header,client4_creat.plan.ref-data,client4_creat.plan.line.view,client4_creat.plan.inventory.view,client4_creat.plan.period.lines,client4_creat.plan.products,client4_creat.constraint,client4_creat.separation,client4_creat.plan.guidelines,client4_creat.plan.spots,client4_creat.plan.spot.links,client4_creat.plan.deal.link,client4_creat.plan.line.target,client4_creat.plan.digital.period.lines,client4_creat.plan.digital.lines,client4_creat.plan.deal.link,client4_creat.plan.quarter.view,client4_creat.plan.line.weekly.spots,client4_creat.plan.total.view,client4_creat.plan.weekly.view,client4_creat.condition.status,client4_creat.plan.additionalfees,client4_creat.plan.digital.period.view,client4_creat.plan.line.weekly.totals.view,client4_creat.plan.buyer.goals,client4_creat.plan.period.line.lineage.comments,client4_creat.plan.books,client4_creat.plan.line.attributes",
  "ssl.enabled": "true",
  "members.auto.discover": "true",
  "name": "source_mongodb",
  "ssl.invalid.allowed": "true",
  "snapshot.mode": "never"
}

Demo

Answer 2

Using GNU AWK

$ awk -v client="client2" '
       /"collect.whitelist":/{gsub(client"[^,]*,",""); sub(/[^"],$/,"\",")}1' menu.json

{
    "connect.class": "io.connector.MongoDbConnect",
    "tasks.max": "2",
    "snapshot.delay.ms": "1500",
    "collect.whitelist": "client1_creat.plan.header,client1_creat.plan.ref-data,client1_creat.plan.line.view,client1_creat.plan.inventory.view,client1_creat.plan.period.lines,client1_creat.plan.products,client1_creat.constraint,client1_creat.separation,client1_creat.plan.guidelines,client1_creat.plan.spots,client1_creat.plan.spot.links,client1_creat.plan.deal.link,client1_creat.plan.line.target,client1_creat.plan.digital.period.lines,client1_creat.plan.digital.lines,client1_creat.plan.deal.link,client1_creat.plan.quarter.view,client1_creat.plan.line.weekly.spots,client1_creat.plan.total.view,client1_creat.plan.weekly.view,client1_creat.condition.status,client1_creat.plan.additionalfees,client1_creat.plan.digital.period.view,client1_creat.plan.line.weekly.totals.view,client1_creat.plan.buyer.goals,client1_creat.plan.period.line.lineage.comments,client1_creat.plan.books,client1_creat.plan.line.attributes,client3_creat.plan.header,client3_creat.plan.ref-data,client3_creat.plan.line.view,client3_creat.plan.inventory.view,client3_creat.plan.period.lines,client3_creat.plan.products,client3_creat.constraint,client3_creat.separation,client3_creat.plan.guidelines,client3_creat.plan.spots,client3_creat.plan.spot.links,client3_creat.plan.deal.link,client3_creat.plan.line.target,client3_creat.plan.digital.period.lines,client3_creat.plan.digital.lines,client3_creat.plan.deal.link,client3_creat.plan.quarter.view,client3_creat.plan.line.weekly.spots,client3_creat.plan.total.view,client3_creat.plan.weekly.view,client3_creat.condition.status,client3_creat.plan.additionalfees,client3_creat.plan.digital.period.view,client3_creat.plan.line.weekly.totals.view,client3_creat.plan.buyer.goals,client3_creat.plan.period.line.lineage.comments,client3_creat.plan.books,client3_creat.plan.line.attributes,client4_creat.plan.header,client4_creat.plan.ref-data,client4_creat.plan.line.view,client4_creat.plan.inventory.view,client4_creat.plan.period.lines,client4_creat.plan.products,client4_creat.constraint,client4_creat.separation,client4_creat.plan.guidelines,client4_creat.plan.spots,client4_creat.plan.spot.links,client4_creat.plan.deal.link,client4_creat.plan.line.target,client4_creat.plan.digital.period.lines,client4_creat.plan.digital.lines,client4_creat.plan.deal.link,client4_creat.plan.quarter.view,client4_creat.plan.line.weekly.spots,client4_creat.plan.total.view,client4_creat.plan.weekly.view,client4_creat.condition.status,client4_creat.plan.additionalfees,client4_creat.plan.digital.period.view,client4_creat.plan.line.weekly.totals.view,client4_creat.plan.buyer.goals,client4_creat.plan.period.line.lineage.comments,client4_creat.plan.books,client4_creat.plan.line.attributes",
    "ssl.enabled": "true",
    "members.auto.discover": "true",
    "name": "source_mongodb",
    "ssl.invalid.allowed": "true",
    "snapshot.mode": "never"
}