JSON OPERATORS (-> / ->>)JSON
Buy Me A Coffee

Shorthand operators for navigating JSON document structure. -> extracts a JSON value; ->> extracts a text (unquoted) value. Availability and exact semantics vary significantly by engine.

PostgreSQL operators for querying JSONB: arrow for int/object access, arrow-arrow for text extraction, at-greater for containment, question-mark for key existence. Combined with GIN indexes, these enable powerful indexed JSON querying.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.7.8 col->'$.path' is shorthand for JSON_EXTRACT(col, '$.path') and returns a JSON-encoded value (strings include surrounding quotes). col->>'$.path' is shorthand for JSON_UNQUOTE(JSON_EXTRACT(col, '$.path')) and returns unquoted text. Path expressions must include the $ root prefix.
PostgreSQL ✓ Supported 9.3 Richest operator set of any engine. -> extracts a JSON/JSONB value by key (or array index). ->> extracts as text. #> extracts by path array (JSON result); #>> extracts by path array (text result). jsonb columns additionally support @> (contains), <@ (contained by), ? (key exists), ?| (any key exists), ?& (all keys exist) — all indexable with GIN. The json type supports -> and ->> but not the containment operators.
SQL Server ✗ Not Supported — No -> or ->> operators. All JSON access is through functions: JSON_VALUE() for scalars and JSON_QUERY() for objects/arrays. Path expressions are string arguments to functions, not operators. There is no operator-based path syntax.
Oracle ✗ Not Supported — No -> or ->> operators in standard SQL access. All access uses JSON_VALUE() and JSON_QUERY() functions with path string arguments. Oracle 21c introduced a native JSON data type with dot-notation access (col.key.subkey), but this only applies to columns declared as JSON type, not VARCHAR2/CLOB with JSON content.
SQLite ✓ Supported 3.38.0 -> and ->> operators added in SQLite 3.38.0 (2022-02-22). SQLite's ->> always returns an SQL value (text, integer, real, or NULL) — it never returns a JSON-encoded string, which differs from MySQL's -> behavior. For SQLite < 3.38.0, use json_extract() exclusively.

Details

PostgreSQL has the most complete operator set: ->, ->>, #>, #>> for extraction plus @>, <@, ?, ?|, ?& for containment and key existence on jsonb (all GIN-indexable). MySQL and SQLite share -> / ->> but with a key difference: MySQL's ->> returns JSON_UNQUOTE output (text) while SQLite's ->> returns a native SQL type directly. SQL Server and Oracle have no operators at all — only functions. Note that MySQL's path expressions require the $ prefix (data->>'$.name') while PostgreSQL's do not (data->>'name').

Standard Syntax

-- PostgreSQL (json/jsonb): SELECT data->'address' AS address_json, data->>'name' AS name_text, data#>'{addr,city}' AS city_json, data#>>'{addr,city}' AS city_text FROM users; -- MySQL: SELECT data->'$.name' AS name_json, data->>'$.name' AS name_text FROM users;

Version Support

MySQL: Since 5.7.8 PostgreSQL: Since 9.3 SQL Server: Not supported Oracle: Not supported SQLite: Since 3.38.0

Per-Database Syntax & Notes

MySQL

col->'$.path' is shorthand for JSON_EXTRACT(col, '$.path') and returns a JSON-encoded value (strings include surrounding quotes). col->>'$.path' is shorthand for JSON_UNQUOTE(JSON_EXTRACT(col, '$.path')) and returns unquoted text. Path expressions must include the $ root prefix.

-- -> returns JSON-encoded value (quoted strings): SELECT data->'$.name' AS name_with_quotes FROM users; -- Result: "Alice" -- ->> returns unquoted text: SELECT data->>'$.name' AS name_plain FROM users; -- Result: Alice -- Nested: SELECT data->'$.address.city' AS city_json, data->>'$.address.city' AS city_text FROM users; -- In WHERE: SELECT * FROM users WHERE data->>'$.active' = 'true';

PostgreSQL

Richest operator set of any engine. -> extracts a JSON/JSONB value by key (or array index). ->> extracts as text. #> extracts by path array (JSON result); #>> extracts by path array (text result). jsonb columns additionally support @> (contains), <@ (contained by), ? (key exists), ?| (any key exists), ?& (all keys exist) — all indexable with GIN. The json type supports -> and ->> but not the containment operators.

-- Key extraction: SELECT data->'name' AS name_json, data->>'name' AS name_text FROM users; -- Array index: SELECT data->'tags'->0 AS first_tag_json, data->'tags'->>0 AS first_tag_text FROM posts; -- Nested path (path array): SELECT data#>'{address,city}' AS city_json, data#>>'{address,city}' AS city_text FROM users; -- jsonb containment / existence: SELECT * FROM users WHERE data @> '{"role":"admin"}'; SELECT * FROM users WHERE data ? 'email'; SELECT * FROM users WHERE data ?| ARRAY['admin','superuser'];

SQL Server

No -> or ->> operators. All JSON access is through functions: JSON_VALUE() for scalars and JSON_QUERY() for objects/arrays. Path expressions are string arguments to functions, not operators. There is no operator-based path syntax.

-- Must use functions instead of operators: SELECT JSON_VALUE(data, '$.name') AS name, JSON_QUERY(data, '$.address') AS address_obj FROM users; -- No shorthand; every access needs the full function call: SELECT JSON_VALUE(data, '$.address.city') AS city FROM users WHERE JSON_VALUE(data, '$.active') = 'true';

Oracle

No -> or ->> operators in standard SQL access. All access uses JSON_VALUE() and JSON_QUERY() functions with path string arguments. Oracle 21c introduced a native JSON data type with dot-notation access (col.key.subkey), but this only applies to columns declared as JSON type, not VARCHAR2/CLOB with JSON content.

-- Function-based access only: SELECT JSON_VALUE(data, '$.name') AS name, JSON_QUERY(data, '$.address') AS address_obj FROM users; -- Oracle 21c dot notation (JSON type columns only): SELECT u.data.name.string(), u.data.address.city.string() FROM users u;

SQLite

-> and ->> operators added in SQLite 3.38.0 (2022-02-22). SQLite's ->> always returns an SQL value (text, integer, real, or NULL) — it never returns a JSON-encoded string, which differs from MySQL's -> behavior. For SQLite < 3.38.0, use json_extract() exclusively.

-- -> returns JSON encoding: SELECT data->'name' AS name_json FROM users; -- Result: "Alice" (with quotes) -- ->> returns SQL value: SELECT data->>'name' AS name_text FROM users; -- Result: Alice (no quotes) -- Chained: SELECT data->'address'->>'city' AS city FROM users; -- Fallback for older SQLite: SELECT json_extract(data, '$.name') AS name FROM users;