Skip to content

JSON processing

JSON Operators

Demo data
{
  "firstName": "Gonzo",
  "lastName": "Puppet",
  "age": 81,
  "streetAddress": "100 Internet Dr",
  "city": "MuppetTown",
  "state": "MP",
  "postalCode": "12345",
  "phoneNumbers": [
    {
      "mobile": "111-111-1111"
    },
    {
      "home": "222-222-2222"
    }
  ]
}
Description Operator Example
field in JSON object -> text demo -> 'streetAddress'
"100 Internet Dr"
element in JSON array -> integer demo -> 'phoneNumbers' -> 1
'{ "home": "222-222-2222" }'
deep access by path segments #> text[] demo #> '{phoneNumbers, 1, home}'
"222-222-2222"

Retrieving text values instead of JSON values

Extending the operator with a second > character, i.e. ->>, #>>, returns the JSON value as text rather than as JSONB.

Description Operator Example
contains JSON ? @> jsonb demo @> '{"firstName": "Gonzo"}'::jsonb
true
is JSON contained ? <@ jsonb '{"firstName": "Gonzo"}'::jsonb <@ demo
true
contains top-level key ? ? text demo ? 'fullName'
false
contains any top-level key ? ?| text[] demo ?| '{fullName, firstName, lastName}'
true
contains all top-level keys ? ?& text[] demo ?& '{fullName, firstName, lastName}'
false
Operator Example
@? jsonpath demo @? '$.age ? (@ > 80)'
true
@@ jsonpath demo @@ '$.age > 80'
true

JSON Functions