Postgresql has support for binary json (as of 9.4). For Python users using Psycopg, see using json.
Basics
Jsonb operators
| `Operator | Description |
|---|---|
-> | Get JSON array element (indexed from zero, negative integers count from the end) |
-> | Get JSON object field by key. |
->> | Get JSON array element as text. |
->> | Get JSON object field as text. |
#> | Get JSON object at the specified path. |
#>> | Get the JSON object on the specified path as text. |
@> | Does the left JSON value contain the right path/value entries at the top level? |
<@ | Are the left JSON path/value entries at the top level within the right JSON value? |
? | Does the string exist as a top-level key within the JSON value? |
?| | Do any of these array strings exist as top-level keys? |
?& | Do all of these array strings exist as top-level keys? |
|| | Concatenate two JSONB values into a new JSONB value. |
– | Delete key/value pair or string element from the left operand. Key/value pairs are matched based on their key value. |
– | Delete multiple key/value pairs or string elements from the left operand. Key/value pairs are matched based on their key value. |
– | Delete the array element with the specified index (Negative integers count from the end). It throws an error if the top-level container is not an array. |
#- | Delete the field or element with the specified path (for JSON arrays, negative integers count from the end) |
@? | Does the JSON path return any item for the specified JSON value? |
@@ | Returns the result of the JSON path predicate check for the specified JSON value. Only the first item of the result is taken into account. If the result is not Boolean, then null is returned. |
Discussions
Jsonb vs Columns
- Traditional columns are more efficient.
- Postgres doesn't store column statistics for jsonb columns.
- Jsonb has a larger storage footprint for the same data.
- If a key occurs frequently, consider moving it to its own column.
Json vs Jsonb
- JSON preserves the original formatting (a.k.a whitespace) and ordering of the keys.
- JSON preserves duplicate keys.
- JSON is faster to ingest than JSONB; however, if you do any further processing, JSONB will be faster.