PostgreSQL 18 reference · 21 entries

PostgreSQL JSON Functions

PostgreSQL JSON functions and operators: ->, ->>, #>>, @>, jsonb_build_object(), jsonb_set(), jsonb_agg(), jsonb_path_query() and JSON_TABLE().

Prefer jsonb over json: it's stored parsed, supports indexing (GIN) and has more operators. PostgreSQL 16 and 17 added the SQL-standard functions JSON_OBJECT(), JSON_VALUE(), JSON_QUERY(), JSON_EXISTS() and JSON_TABLE().

Name	Description	Since
`-> and ->>`	Read JSON with -> (returns jsonb) or ->> (returns text) by object key or array index. Negative indexes count from the end.	9.3
`#> and #>>`	Read a nested JSON value by a path array: #> returns jsonb and #>> returns text, as in data #>> '{user,name}'.	9.3
`@> and ?`	Test JSON containment with @> (left contains right) and key existence with ?, both of which a GIN index can speed up.	9.4
`JSONB_BUILD_OBJECT()`	Build a JSON object from alternating keys and values. json_build_object() returns json instead of jsonb.	9.5
`JSONB_BUILD_ARRAY()`	Build a JSON array from the arguments, which may be of different types.	9.5
`TO_JSONB()`	Convert any SQL value, including a whole row, to jsonb. Rows become objects with column names as keys.	9.5
`JSONB_AGG()`	Aggregate a group's values or rows into a JSON array. json_agg() does the same but returns json.	9.5
`JSONB_OBJECT_AGG()`	Aggregate key/value pairs from a group into one JSON object, such as a name-to-price lookup.	9.5
`JSONB_SET()`	Return jsonb with the value at a path replaced or, by default, created. Combine with \|\| to merge objects.	9.5
`JSONB_INSERT()`	Insert a value into a JSON array at a path (before or after an element) or add a new object key.	9.6
`JSONB_STRIP_NULLS()`	Remove object fields whose value is JSON null, recursively. Since PostgreSQL 18 it can also drop nulls in arrays.	9.5
`JSONB_EACH()`	Expand a JSON object into one row per key, with key and value columns. jsonb_each_text() returns text values.	9.4
`JSONB_ARRAY_ELEMENTS()`	Expand a JSON array into one row per element. jsonb_array_elements_text() returns the elements as text.	9.4
`JSONB_ARRAY_LENGTH()`	Return the number of elements in a JSON array. Raises an error for objects and scalars.	9.4
`JSONB_TYPEOF()`	Return the type of the top-level JSON value: object, array, string, number, boolean or null.	9.4
`JSONB_PRETTY()`	Format a jsonb value as indented, multi-line text that is easy for people to read, such as in logs or reports.	9.5
`JSONB_PATH_QUERY()`	Return every JSON item matched by an SQL/JSON path expression, such as '$.items[*] ? (@.price > 10)'.	12
`JSON_TABLE()`	Turn JSON into a relational table in the FROM clause, with typed columns taken from SQL/JSON paths. Added in PostgreSQL 17.	17
`JSON_VALUE()`	Extract one scalar from JSON with an SQL/JSON path and return it as a chosen SQL type. Added in PostgreSQL 17.	17
`JSON_QUERY()`	Extract an object, array or other JSON fragment with an SQL/JSON path, optionally wrapping several items in an array.	17
`JSON_EXISTS()`	Return true if an SQL/JSON path finds anything in a JSON document. Added in PostgreSQL 17.	17