Tech Optimizer
July 2, 2026
A dataset of over 100,000 company profiles was found to have approximately 150,000 corrupted values across ten jsonb columns due to incorrect serialization caused by using JSON.stringify on values intended for jsonb columns. The problematic code was `await sql` update listings set services = ${JSON.stringify(record.services)} where id = ${record.id}`. This resulted in jsonb strings instead of jsonb arrays. The issue was detected by querying the type of the jsonb columns, revealing that two frequently updated columns had around 105,000 and 41,000 corrupted values, respectively. The repair process involved extracting the inner text and re-casting it as valid jsonb, while also addressing complications such as NUL bytes and double-encoding. The correct method for inserting jsonb values is to use `sql.json(record.services)` instead of JSON.stringify. Regular type checks are now part of a daily integrity job to prevent similar issues.
