Builds a JSON object from key–value pairs.
JSONB_BUILD_OBJECT(<key1>, <value1>, <key2>, <value2>, …)
The arguments come in pairs: a key, then its value. Numbers stay numbers, text becomes strings, NULL becomes null. Handy for sending data to the application in a single JSON field or gathering several columns into one object.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| employee |
|---|
| {"name":"Emma","salary":1200} |
| employee |
|---|
| {"name":"Emma","phone":"+44 7700 900123","department":"backend"} |
| {"name":"Liam","phone":null,"department":"backend"} |
| {"name":"Olivia","phone":"+44 7700 900456","department":"frontend"} |
| {"name":"Noah","phone":"+44 7700 900789","department":"frontend"} |
| {"name":"Sophia","phone":null,"department":"design"} |
Where there's no phone number, the JSON has null.
| name |
|---|
| Emma |
->> gets the value for a key as text.
There must be an even number of arguments: JSONB_BUILD_OBJECT('name', 'Emma', 'salary') throws argument list must have even number of elements.
jsonb orders the keys itself and doesn't keep duplicates: JSONB_BUILD_OBJECT('b', 1, 'a', 2) shows {"a": 2, "b": 1}. The written order is kept by JSON_BUILD_OBJECT — without the B.
-> returns jsonb, and ->> returns text. To compare with a string or calculate, you need ->>, and for numbers a cast as well: (employee ->> 'salary')::int.