JSON Operators
Table of Contents
Semantics
- Dot
.is used for member access - Square brackets
[]are used for array access - SQL/JSON arrays are 0-based indexed (unlike regular SQL arrays that start from 1)
Operators1
| Operator | Usage | Description |
|---|---|---|
| -> | json(b) -> integer = json(b) | Extracts n’th element of JSON array |
| -> | json(b) -> text = json(b) | Extracts JSON object field with key |
| ->> | json(b) ->> integer = text | Extracts n’th element of JSON array, as text |
| ->> | json(b) ->> text = text | Extracts JSON object field with key, as text |
| #> | json(b) #> text[] = json | Extracts JSON sub-object at path |
| #>> | json(b) #» text[] = text | Extracts JSON sub-object at path, as text |
| @> | jsonb @> jsonb = boolean | Does the first JSON contain the second? |
Examples
| Operator | Operation | Result |
|---|---|---|
| -> | ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]'::json -> 2 | {“c”:“baz”} |
| -> | ‘{“a”: {“b”:“foo”}}'::json -> ‘a’ | {“b”:“foo”} |
| ->> | ‘[1,2,3]'::json ->> 2 | 3 |
| ->> | ‘{“a”:1,“b”:2}'::json ->> ‘b’ | 2 |
| #> | ‘{“a”: {“b”: [“foo”,“bar”]}}'::json #> ‘{a,b,1}’ | “bar” |
| #>> | ‘{“a”: {“b”: [“foo”,“bar”]}}'::json #>> ‘{a,b,1}’ | bar |
| @> | ‘{“a”:1, “b”:2}'::jsonb @> ‘{“b”:2}'::jsonb | t |
| <@ | ‘{“b”:2}'::jsonb <@ ‘{“a”:1, “b”:2}'::jsonb | t |