Product Documentation

Appearance

All platforms

JSON Accessor ​

The : operator can be used to path into a JSONB expression and extract values out of it. The syntax is <jsonb_expr> : <jsonpath_expr> <error handling mode>. The error handling mode is optional. The return type is JSONB.

JSONPath expressions ​

JSONPath expressions can only appear after the : operator.

Root Identifier ​

A JSONPath must always start with $, which refers to the root node of the input JSONB.

sql
SELECT '{"name":"Slovakia","currency":"€"}' : $;
--  {"currency":"€","name":"Slovakia"}

Name Selector ​

A name selector selects a value from an object with the matching key. There are three ways to use a name selector.

  1. <jsonpath_expr>.name - dot notation with an identifier
  2. <jsonpath_expr>.'name' - dot notation with a string constant
  3. <jsonpath_expr>['name'] - bracket notation with a string constant
sql
WITH cte AS (SELECT '{"k":"v"}'::JSONB AS jsonb)
SELECT jsonb : $.k, jsonb : $.'k', jsonb : $['k']
FROM cte;
--  "v" | "v" | "v"

The first way is the most concise but also the least powerful, because some names cannot be used in the grammar in that context.

sql
SELECT '{"1":"one"}' : $.1;
-- ERROR:  syntax error at or near ".1"
-- LINE 1: SELECT '{"1":"one"}' : $.1;
                                ^
SELECT '{"1":"one"}' : $.'1';
--  "one"

When using dot notation with an identifier, the identifier is case sensitive:

sql
SELECT '{"Age":25}' : $.Age;
--  25

Index Selector ​

An index selector selects a value from an array at the given index. The first element has index 0.

sql
SELECT '[3,1,4]' : $[2];
--  4

Error Handling Modes ​

Two different behaviors are available for when a JSONPath is not able to find a value. The two behaviors are

  1. Return NULL
  2. Error

The default behavior can be modified by setting the json_missing_element_default configuration parameter:

sql
SET json_missing_element_default TO 'null';

SELECT '{}' : $.k;
-- <NULL>

SET json_missing_element_default TO 'error';

SELECT '{}' : $.k;
-- ERROR:  No key found at path: 'strict $."k"' for '{}'

The behavior can also be explicitly set after the JSONPath expression, which has priority over the value set by the configuration parameter

sql
SELECT '{}' : $.k NULL ON ERROR;
-- <NULL>

SELECT '{}' : $.k ERROR ON ERROR;
-- ERROR:  No key found at path: 'strict $."k"' for '{}'