PostgreSQL 18 reference · JSON Functions

PostgreSQL JSON_VALUE() Function

Extract one scalar from JSON with an SQL/JSON path and return it as a chosen SQL type. Added in PostgreSQL 17.

PostgreSQL 18 Since 17

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

JSON_VALUE(json, path RETURNING type [DEFAULT ... ON EMPTY | ON ERROR])

Parameter	Description
`json`	The JSON document.
`path`	A path to a single scalar.
`RETURNING` optional	The SQL type of the result. Default: `text`

A scalar of the requested type.

Typed extraction

SELECT JSON_VALUE('{"price":"12.50"}', '$.price' RETURNING numeric) * 2 AS doubled;

Output

 doubled
---------
   25.00
(1 row)

Run this on your computer — see “Get Started” for setup.

A default when missing

SELECT JSON_VALUE('{"a":1}', '$.missing' DEFAULT 'none' ON EMPTY) AS with_default;

Output

 with_default
--------------
 none
(1 row)

Run this on your computer — see “Get Started” for setup.

Version	Change
PostgreSQL 17	Added

JSON_QUERY() · Extract an object, array or other JSON fragment with an SQL/JSON path, optionally wrapping several items in an array.
JSON_EXISTS() · Return true if an SQL/JSON path finds anything in a JSON document. Added in PostgreSQL 17.
-> and ->> · Read JSON with -> (returns jsonb) or ->> (returns text) by object key or array index. Negative indexes count from the end.
SQL String Functions · tutorial