A PostgreSQL 18 survey report averages JSON_VALUE(answers, '$.nps' RETURNING int) and the number looks wrong; what is JSON_VALUE doing with bad answers, and how do you expose them?
answer
- ON EMPTY and ON ERROR defaults
- silent NULL, AVG skips it
- ERROR ON EMPTY for missing keys
- a cast is stricter, except null
basics
~20 sJSON_VALUE defaults to NULL ON EMPTY and NULL ON ERROR, so a missing nps, a JSON null and an unconvertible value like "ten" all become SQL NULL, which AVG silently skips. Add ERROR ON EMPTY and ERROR ON ERROR, or count the NULLs.
solid answer
~50 s`JSON_VALUE`, one of the SQL/JSON query functions added in PostgreSQL 17, returns one scalar and coerces it to the `RETURNING` type. When the path finds nothing it applies `ON EMPTY`; when evaluation or coercion fails it applies `ON ERROR`; both default to `NULL`, and a JSON `null` also comes back as SQL NULL. So answers lacking `nps`, holding `null` or holding `"ten"` all become NULL, and `AVG` ignores NULLs: the average covers only clean rows and raises nothing. Investigate with `ERROR ON EMPTY ERROR ON ERROR`, tag bad rows with `DEFAULT -1 ON ERROR`, and count `jsonb_typeof(answers->'nps')` by type. A plain cast differs: `(answers->'nps')::int` raises `cannot cast jsonb string to type integer` for any string; for a JSON `null` it raised an error in 17 but returns NULL in 18. `JSON_TABLE` columns share the same NULL defaults.
code
sql · 8 lines-- How much of the data does the average really cover?
SELECT count(*) AS responses,
count(JSON_VALUE(answers, '$.nps' RETURNING int)) AS usable_nps,
avg(JSON_VALUE(answers, '$.nps' RETURNING int)) AS avg_nps,
count(*) FILTER (WHERE jsonb_typeof(answers->'nps') = 'string') AS string_nps,
count(*) FILTER (WHERE NOT answers ? 'nps') AS missing_nps
FROM form_responses
WHERE form_id = 42;go deeper
Recall that JSON_VALUE extracts one scalar with a path and can return a chosen type, and that by default it returns NULL instead of failing.
Explain ON EMPTY versus ON ERROR, their NULL defaults, how lax mode turns a missing key into an empty result, and how JSON_TABLE turns arrays into typed rows.
Diagnose silent loss in aggregates: profile values with jsonb_typeof, make report queries state their ON ERROR behaviour, and know that 18's jsonb null cast changed some casts and CHECKs.
Treat NULL-on-error defaults as a data-quality policy: decide which pipelines must fail, which tag and count bad values, and how reports disclose the rows they excluded.
## SQL/JSON query functions PostgreSQL 17 added the SQL-standard query functions `JSON_EXISTS`, `JSON_QUERY` and `JSON_VALUE`, and the table function `JSON_TABLE`. Each applies an SQL/JSON path to a context item, usually a `jsonb` column, and has explicit clauses for what to do when the path finds nothing (`ON EMPTY`) or something goes wrong (`ON ERROR`): | Function | Returns by default | `ON EMPTY` default | `ON ERROR` default | |---|---|---|---| | `JSON_EXISTS` | `boolean` | not applicable | `FALSE` | | `JSON_QUERY` | `jsonb` | `NULL` | `NULL` | | `JSON_VALUE` | `text` | `NULL` | `NULL` | | `JSON_TABLE` value column | the column's type | `NULL` | `NULL` | ## Why the report is misleading Take `AVG(JSON_VALUE(answers, '$.nps' RETURNING int))` over a survey's submissions. Four kinds of rows exist: 1. `"nps": 9`, a number: `JSON_VALUE` returns 9. 2. `"nps": "8"`, a string that converts: `JSON_VALUE` returns 8, because it coerces through the target type's input conversion. 3. `"nps": null`, or no `nps` key at all: SQL NULL. A JSON null comes back as SQL NULL, and a missing key is an empty result in the default lax mode, handled by `NULL ON EMPTY`. 4. `"nps": "ten"` or `"nps": [9, 10]`: the coercion fails, or the result is not a single scalar. That is an error, handled by the default `NULL ON ERROR`. `AVG` ignores NULLs, so groups 3 and 4 drop out of both the sum and the count. If the respondents who typed "ten" came from an older form version with a free-text field, the average now describes only the newer form, and nothing in the result says so. ## Exposing the bad rows - **Fail loudly** while investigating: `JSON_VALUE(answers, '$.nps' RETURNING int ERROR ON EMPTY ERROR ON ERROR)` stops at the first bad row and names the problem. `ERROR ON ERROR` alone is not enough for missing keys, because in lax mode those are empty results, not errors. - **Tag instead of failing**: `DEFAULT -1 ON ERROR` turns unconvertible values into a sentinel you can count and exclude explicitly. - **Profile by type**: `SELECT jsonb_typeof(answers->'nps'), count(*) FROM form_responses GROUP BY 1` separates `number`, `string`, `null` (a JSON null) and SQL NULL (a missing key). - **Report coverage**: show `count(*)` next to `count(JSON_VALUE(...))` so readers see how many rows the average covers. ## The cast is stricter, and 18 changed it A plain cast from `jsonb` behaves differently from `JSON_VALUE`: - `(answers->'nps')::int` accepts only a JSON number. It raises `cannot cast jsonb string to type integer` for `"8"` as well as for `"ten"`. - For a JSON `null`, PostgreSQL 17 raised `cannot cast jsonb null to type integer`; **PostgreSQL 18 returns SQL NULL**. A query that failed on 17 because of null answers succeeds on 18 and silently drops them, as `JSON_VALUE` does. - For a missing key, `->` already returns SQL NULL, so the cast returns NULL in both versions. - `(answers->>'nps')::int` goes through text: `'8'` converts, while `'ten'` fails with `invalid input syntax for type integer`. ## JSON_TABLE for reports `JSON_TABLE` turns each document into rows with typed columns, laterally joined to the row it came from. Each value column behaves like `JSON_VALUE` or `JSON_QUERY`, with the same NULL defaults, so state the behaviour you want: ```sql SELECT r.id, jt.* FROM form_responses r, JSON_TABLE(r.answers, '$.ratings[*]' COLUMNS (n FOR ORDINALITY, field text PATH '$.field', score int PATH '$.score' DEFAULT -1 ON ERROR)) AS jt WHERE r.form_id = 42; ``` `NESTED PATH` unnests deeper arrays in the same call, and `EXISTS PATH` columns return booleans with a default of `FALSE ON ERROR`. Any report query that feeds a decision should spell out its `ON EMPTY` and `ON ERROR` behaviour.
- What is the difference between JSON_VALUE and JSON_QUERY for the same path?`JSON_VALUE` returns one scalar, as `text` unless `RETURNING` says otherwise, with string quotes removed; a JSON `null` becomes SQL NULL, and an object, array or several items is an error. `JSON_QUERY` returns `jsonb`, can return objects and arrays, keeps string quotes unless `OMIT QUOTES` is given, returns a JSON `null` as is, and needs `WITH WRAPPER` to return several items.
- After upgrading from 17 to 18, how can a CHECK constraint such as CHECK ((answers->'nps')::int BETWEEN 0 AND 10) behave differently?On 17, a row with `"nps": null` made the cast raise an error, so the insert failed. On 18 the cast returns NULL, the CHECK expression is NULL rather than false, and a CHECK passes on NULL, so the row is accepted. If null answers must be rejected, test for them explicitly, for example with `jsonb_typeof(answers->'nps') = 'number'`.
- JSON_VALUE raises an error by default when a value cannot be converted
- ERROR ON ERROR also catches a missing key in lax mode
- Casting a jsonb null to int raises an error in PostgreSQL 18
- JSON_TABLE is new in PostgreSQL 18
- (answers->'nps')::int accepts the JSON string "8" just like JSON_VALUE
Assumes PostgreSQL 18; JSON_VALUE and JSON_TABLE arrived in 17, and 18 made a cast of a jsonb null to a scalar type return NULL instead of an error.