Quoted JSON Values vs Bare Text

Concepts covered: JSON Extraction, Type Casting, Inner Joins

Extract event 2's event_type in 2 ways and you get 2 different answers: "purchase", 10 characters including the double quotes, or purchase, 8 characters. The first is a JSON value: the engine cut a piece out of the document and handed it back still written as JSON, and JSON writes a string inside quotes. The second is SQL text: the engine unwrapped the JSON string into a plain value that compares equal to the literal 'purchase'.

Spark's get_json_object offers only one form: it returns a string, with the quotes already stripped from string values. Every other engine has both forms. Only the function or operator name tells you which one you wrote.

Why the quoted form exists

You walk the path with the JSON-returning form. Extract $.device or $.items and there is no bare text to unwrap: the result is an object or an array, and the next step needs it to still be JSON. So the choice splits by job. Containers and intermediate steps stay JSON; the final scalar that a later clause filters or joins on comes out as text. Postgres builds that rule into its operators: -> on each step that lands on a container, ->> on the step that lands on the value you want.

The comparison that returns nothing

The quoted form is dangerous because it usually runs. In BigQuery, JSON_EXTRACT over a STRING payload returns a STRING, so WHERE JSON_EXTRACT(payload, '$.event_type') = 'purchase' is a legal comparison between "purchase" and purchase. It's false on every row, the query reports 0 purchases, and nothing errors. Postgres reaches the same place through a cast: (payload->'user_id')::text is the 7-character string "u_101", and a join on it matches no user.

-- Postgres: 2 ways to join events to users

-- Matches nothing: the left side keeps its JSON quotes
SELECT COUNT(*) AS matched
FROM events e
JOIN users u ON (e.payload->'user_id')::text = u.user_id;  -- '"u_101"' vs 'u_101'

-- Matches: ->> returns bare text
SELECT COUNT(*) AS matched
FROM events e
JOIN users u ON e.payload->>'user_id' = u.user_id;         -- 'u_101' vs 'u_101'

Run both against the 4 events from the first section and a users table holding u_101 and u_102. The first query returns 0. The second returns 4, one match per event. The only difference between them is a single character in the operator.

Numbers have no quotes to strip

Quotes appear only on JSON strings. A JSON number or boolean prints the same in both forms, as does null, which is why both amount rows show 49.90. What differs is the type: one is jsonb and the other text. Neither is NUMERIC. SUM(payload->>'amount') fails in Postgres because there is no SUM over text. The habit is to extract with the text form and cast on the same CTE line, (payload->>'amount')::NUMERIC AS amount, so every later clause sees a number.

MySQL arrows and Snowflake variants

MySQL puts both forms behind arrows that resemble Postgres's but take a path string: payload->'$.event_type' is JSON_EXTRACT and returns "purchase", while payload->>'$.event_type' is JSON_UNQUOTE(JSON_EXTRACT(...)) and returns purchase. Moving between the 2 engines, say which one you mean, since payload->>'event_type' without the $. is valid Postgres and an error in MySQL. In Snowflake, payload:event_type is a VARIANT that a result grid prints with quotes; ::STRING at the end gives the column a string type for everything downstream.

The sentence that scores

Say why you chose the operator: 'I'm using ->> because I'm comparing to a string literal; -> would keep the JSON quotes.' When a filter or join comes back empty, the first suspect worth naming is a quoted JSON value being compared to bare text. Some interviewers plant this deliberately, writing a query with the JSON-returning function and asking why the count is 0.

About this lesson

This section is part of the Semi-Structured Data: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.