json

`-> ->> #> #>> (JSON accessors)`

Postgres JSON navigation operators. Single-arrow -> and ->> take one key or index; hash-arrows #> and #>> take a text-array path for deep access. The double-arrow forms (->>, #>>) always return text; the single-arrow forms return jsonb.

Syntax

col -> 'key'  |  col ->> 'key'  |  col #> '{a,b}'  |  col #>> '{a,b}'

Parameters / Modifiers

Parameter	Purpose
->	Return jsonb subvalue (still typed as JSON)
->>	Return the value cast to text
#>	Deep path access returning jsonb
#>>	Deep path access returning text
MySQL col->'$.key'	MySQL 5.7+ shorthand for JSON_EXTRACT with a $.path expression

Examples

SELECT data -> 'name' FROM users;

Returns the name as jsonb (with quotes if it's a string)

SELECT data ->> 'name' FROM users;

Returns the name as plain text (no surrounding quotes)

SELECT data #> '{prefs,theme}' FROM users;

Deep-path fetch — returns the theme value as jsonb

SELECT data #>> '{items,0,id}' FROM orders;

First item's id as text; array indices are string digits inside the path

Dialect notes / Gotcha

-> vs ->> is the classic gotcha: single arrow returns jsonb (still quoted for strings), double arrow returns unwrapped text. Path segments are always text — use '0' not 0 for array indices, and missing paths return NULL rather than erroring.

Related functions

jsonb_set

Postgres function that returns a copy of target with the value at path replaced by new_value. Creates the key if create_missing (default true); paths use text arrays like '{users,0,name}'.

jsonb_agg

Postgres aggregate that collects rows into a jsonb array. Its sibling jsonb_object_agg builds a jsonb object from key/value pairs.

COALESCE

Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.

← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet