SQL query to update specific JSON values in multiple rows?

Data and Databases
1

I need help writing an SQL query to change password values in JSON data across multiple rows. I want to keep all other data intact and only replace the password with asterisks.

Here’s what I’m trying to do:

-- This is my current query, but it doesn't modify the data
SELECT CustomerID, JsonData 
FROM Customers
WHERE JsonData LIKE '%password%'

My JSON data looks like this:

{
  "user": "johndoe",
  "email": "johndoe@example.com",
  "password": "johndoePassword"
}

I want to change “johndoePassword” to “*****” for all matching rows. I’ve looked at JSON_MODIFY but I’m not sure how to use it for this. Can anyone help me figure out the right SQL statement?

2

hey there SwimmingFish! try running:

UPDATE Customers
SET JsonData = JSON_MODIFY(JsonData, ‘$.password’, ‘*****’)
WHERE JsonData LIKE ‘%password%’

This updates the password to asterisks in matching rows. hope it works for u!

3

Hmmm, interesting question! :thinking: Have u considered using a regex pattern to match the password value specifically? Something like:\n\nUPDATE Customers\nSET JsonData = REPLACE(JsonData, ‘“password”:“[^"]*”’, ‘“password”:“*****”’)\nWHERE JsonData LIKE ‘%password%’\n\nWhat do you think? any concerns about this approach?

4

I recommend using JSON_MODIFY in combination with JSON_VALUE for a more robust solution:

UPDATE Customers
SET JsonData = JSON_MODIFY(JsonData, ‘$.password’, REPLICATE(‘*’, LEN(JSON_VALUE(JsonData, ‘$.password’))))
WHERE JSON_VALUE(JsonData, ‘$.password’) IS NOT NULL

This query updates only the rows where a password exists, keeps the same length as the original password, and avoids the risk of false positives that can occur with LIKE or REPLACE methods. It is recommended to test this solution on a backup first and to consider using proper encryption for storing passwords in the future.