Postgres JSON_QUERY() Function

The JSON_QUERY() function in PostgreSQL 17 provides a powerful way to extract and transform JSON values using SQL/JSON path expressions. This function offers fine-grained control over how JSON values are extracted and formatted in the results.

Use JSON_QUERY() when you need to:

Extract specific values from complex JSON structures
Handle multiple values in results
Control JSON string formatting
Handle empty results and errors gracefully

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Function signature

The JSON_QUERY() function uses the following syntax:

JSON_QUERY(  context_item, -- Input JSON/JSONB data  path_expression -- SQL/JSON path expression  [ PASSING { value AS varname } [, ...] ]  [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]  [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]  [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]  [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON EMPTY ]  [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON ERROR ] ) → jsonb

Understanding Wrappers and Quotes

Wrapper Behavior

By default, JSON_QUERY() does not wrap results (equivalent to WITHOUT WRAPPER). There are three wrapper modes:

WITHOUT WRAPPER (default):
- Returns unwrapped values
- Throws an error if multiple values are returned
WITH UNCONDITIONAL WRAPPER (same as WITH WRAPPER):
- Always wraps results in an array
- Even single values are wrapped
WITH CONDITIONAL WRAPPER:
- Only wraps results when multiple values are present
- Single values remain unwrapped

Quote Behavior

For scalar string results:

By default, values are surrounded by quotes (making them valid JSON)
KEEP QUOTES: Explicitly keeps quotes (same as default)
OMIT QUOTES: Removes quotes from the result
Cannot use OMIT QUOTES with any WITH WRAPPER option

Example usage

Let's explore these behaviors using a sample dataset:

CREATE TABLE users (  id SERIAL PRIMARY KEY,  data JSONB );  INSERT INTO users (data) VALUES ('{  "profile": {  "name": "John Doe",  "contacts": {  "email": ["john@example.com", "john.doe@work.com"],  "phone": "+1-555-0123"  }  } }');

Working with single values

-- Default behavior (unwrapped, quoted) SELECT JSON_QUERY(  data,  '$.profile.contacts.email[0]' ) FROM users;

# | json_query ------------------------ 1 | "john@example.com"

-- With unconditional wrapper SELECT JSON_QUERY(  data,  '$.profile.contacts.email[0]'  WITH WRAPPER ) FROM users;

# | json_query ------------------------ 1 | ["john@example.com"]

Working with multiple values

-- Must use wrapper for multiple values SELECT JSON_QUERY(  data,  '$.profile.contacts.email[*]'  WITH WRAPPER ) FROM users;

# | json_query ----------------------------------------------------- 1 | ["john@example.com", "john.doe@work.com"]

-- This will error (multiple values without wrapper) SELECT JSON_QUERY(  data,  '$.profile.contacts.email[*]'  ERROR ON ERROR ) FROM users;

ERROR: JSON path expression in JSON_QUERY should return single item without wrapper (SQLSTATE 22034) HINT: Use the WITH WRAPPER clause to wrap SQL/JSON items into an array.

Using conditional wrapper

-- Single value with conditional wrapper SELECT JSON_QUERY(  data,  '$.profile.contacts.phone'  WITH CONDITIONAL WRAPPER ) FROM users;

# | json_query ------------------- 1 | "+1-555-0123"

-- Multiple values with conditional wrapper SELECT JSON_QUERY(  data,  '$.profile.contacts.email[*]'  WITH CONDITIONAL WRAPPER ) FROM users;

# | json_query ----------------------------------------------------- 1 | ["john@example.com", "john.doe@work.com"]

Quote handling

-- Default (quoted) SELECT JSON_QUERY(  data,  '$.profile.contacts.phone' ) FROM users;

# | json_query ------------------- 1 | "+1-555-0123"

-- Without quotes (must not use with wrapper) SELECT JSON_QUERY(  data,  '$.profile.contacts.phone'  RETURNING TEXT  OMIT quotes ) FROM users;

# | json_query ------------- 1 | +1-555-0123

Using the PASSING clause

-- Extract array element using a variable SELECT JSON_QUERY(  '[1, [2, 3], null]',  'lax $[*][$idx]'  PASSING 1 AS idx  WITH CONDITIONAL WRAPPER );

# | json_query ------------- 1 | 3

Handling empty results

-- Return custom value when path doesn't match SELECT JSON_QUERY(  '{"a": 1}',  '$.b'  DEFAULT '{"status": "not_found"}' ON EMPTY );

# | json_query -------------------------------- 1 | {"status": "not_found"}

-- Return empty array when path doesn't match SELECT JSON_QUERY(  '{"a": 1}',  '$.b[*]'  EMPTY ARRAY ON EMPTY );

# | json_query ------------- 1 | []

Error handling examples

-- Handle type conversion errors SELECT JSON_QUERY(  '{"value": "not_a_number"}',  '$.value'  RETURNING numeric  NULL ON ERROR );

# | json_query ------------- 1 |

-- Raise error on invalid path SELECT JSON_QUERY(  '{"a": 1}',  'invalid_path'  ERROR ON ERROR );

ERROR: syntax error at end of jsonpath input (SQLSTATE 42601)

Common use cases

Data transformation

-- Transform and validate JSON data CREATE TABLE events (  id SERIAL PRIMARY KEY,  event_data JSONB );  INSERT INTO events (event_data) VALUES ('{  "type": "user_login",  "timestamp": "2024-12-04T10:30:00Z",  "details": {  "user_id": "U123",  "device": "mobile",  "location": {"city": "London", "country": "UK"}  } }');  -- Extract specific fields with custom formatting SELECT  JSON_QUERY(event_data, '$.type' RETURNING TEXT OMIT QUOTES) as event_type,  JSON_QUERY(event_data, '$.details.location' WITH WRAPPER) as location FROM events;

# | event_type | location ------------------------------------- 1 | user_login | [{"city": "London", "country": "UK"}]

Performance considerations

Use appropriate options:
- Use RETURNING TEXT with OMIT QUOTES when JSON formatting is not required
- Choose CONDITIONAL WRAPPER over UNCONDITIONAL when possible
- Consider using DEFAULT expressions for better error recovery
Optimization tips:
- Create indexes on frequently queried JSON paths
- Use specific path expressions instead of wildcards when possible