PostgreSQL JSONB SQL

by Scott, Revised on November 4, 2016 (DatabasesOpen Source)

These notes are just the basics. More details can be found at this summary or in the docs.

Operators

->
Get by index or key: column->1 or column->'key' is used to get a JSON array field by index or object field by key (select column->1 where id = 1). There is a variation of this operator (->>) that coerces the return to text.

#>
Get by keys for nested objects: column#>'{key,subkey}' is used for a nested JSON object field (and #>> for text coercion).

select column->1, column->'key', column#>'{key,subkey}' from table where id = 1

@> and <@
Contains operators for keys, paths and values.

select * from table where data @> '{ "key": "value" }'

?, ?| and ?&
Exists operators for keys. The ?| and ?& are "or" and "and" derivatives.

select * from table where data ? 'key'
select * from table where data->'key' ? 'subkey'

- and #-
Delete an array element, key/value pair by an index, key or path. Used in an update statement.

update table set data = data-1 where id = 1
update table set data = data-'key' where id = 1
update table set data = data#-'{key,subkey}' where id = 1
update table set data = data#-'{key,subkey}'#-'{key,subkey2}' where id = 1

||
The concatenate combines two JSONB objects into one. However, if both objects have the same key, then the value from the right is used. Therefore, it can be used for updating as well as combining though I read it is overkill for a single item and cumbersome for a nested item.

update table set data = data || '{ "key": "value", "key2": "value2" }' where id = 1

Function

jsonb_set()
A function for updating a single field.

update table set data = jsonb_set( data, "{key}", "{value}", true ) where id = 1

Tags: DatabasesOpen Source