I found this bug by accident, which is how I find most of my worst bugs. I was running a routine content sweep across Color Engine, checking that nothing on the blog had quietly broken, and I decided on a whim to check whether every post's FAQ data was actually shaped like an array. Nine posts had FAQs. Three of them weren't.
Not missing. Not null. Just wrong in a way that doesn't throw an error, doesn't show up in a build log, and doesn't make the page crash. That's the part that gets me every time. The scariest bugs in a database aren't the ones that break something visibly. They're the ones that sit there quietly serving the wrong thing to Google for weeks while everything looks fine to you.
What "Double-Encoded JSON" Actually Looks Like
Color Engine stores each post's FAQ content in a Postgres column of type jsonb, which is supposed to hold a real array of question-and-answer objects. That's what the column is for. That's what the app expects when it renders the FAQ section and builds the FAQPage structured data that Google reads for rich results.
Three of those columns didn't hold an array. They held a string. Specifically, a string that was itself a JSON-encoded array, sitting inside a jsonb column that was supposed to hold the real thing. Somewhere upstream, the array had gotten serialized twice before it was ever written to the database. Once when it was built, and once again when something else touched it and didn't realize it was already a string.
If you've never seen this before, it looks completely normal at a glance. Open the row, look at the column, and it still looks like JSON. It still has brackets and quotes in roughly the right places. The difference only shows up when something tries to actually use it as structured data instead of just displaying it as text, and Color Engine's frontend was doing exactly that. It was asking for an array. It was getting a string that merely resembled one.
The Query That Found It
I didn't find this by reading code. I found it by asking Postgres a direct question: for every post, what type is actually in the faqs column?
SELECT id, slug, jsonb_typeof(faqs) FROM blog_posts WHERE jsonb_typeof(faqs) != 'array';
Three rows came back. Three posts where the column's real type, according to Postgres itself, was string instead of array. That's the whole diagnosis. No stack trace needed one, because nothing had ever thrown an error. The FAQ section on those three pages was just quietly rendering nothing useful, and the JSON-LD schema block was quietly emitting broken structured data that Google had presumably been ignoring or misreading since whenever the corruption happened.
The Fix That Doesn't Work, and the One That Does
My first instinct was the obvious one. If the column holds a string that looks like JSON, just cast it back to jsonb, right?
UPDATE blog_posts SET faqs = faqs::text::jsonb WHERE slug = '...';
That does not fix it. I want to sit on that for a second, because it's the part of this story that's actually useful to another builder. Casting a jsonb value to text and back to jsonb doesn't unwrap anything, because the value is already valid jsonb — it just happens to be a jsonb value that contains a string, and that string happens to contain more JSON. The cast is a no-op wearing a costume. It looks like you fixed something. You didn't.
What actually works is unescaping one layer before you cast:
UPDATE blog_posts SET faqs = (faqs #>> '{}')::jsonb WHERE slug = '...';
The #>> '{}' part extracts the jsonb value as plain text at the root path, which strips the outer layer of encoding instead of just relabeling the type. Once you have the real, unescaped JSON string, casting it to jsonb finally gives you back a real array. It's a small, specific trick, and if you've never hit double-encoded jsonb before, there's no reason you'd already know it. I didn't, until I needed it.
Verifying It Actually Worked
I didn't trust the query result on its own. I went and looked at the actual rendered page for all three posts, checked that the FAQ accordion showed real questions and answers instead of nothing, then pulled the page's JSON-LD schema block and confirmed it validated as a real FAQPage array instead of a string. Same check, before and after. The database looking right isn't the same thing as the site being right, and I've been burned by trusting the first one before.
The part I keep coming back to is how invisible this kind of bug is by design. Nothing crashed. Nothing alerted. The three affected posts had been live and getting real traffic the whole time, just quietly worse than they should have been in a way only Google's structured-data parser would notice. If I hadn't run that sweep on a whim, I don't know how long it would have kept going. Probably until someone asked why those three posts never seemed to show FAQ rich results and I went looking for a reason that had been sitting in the database the entire time.
