Converts a value — a number, text, an array or a whole table row — to JSON.
TO_JSONB(<value>)
A number becomes a JSON number, text a JSON string, an array a JSON array. If you pass the table row itself (its alias in the query), you get an object with all the columns — handy for sending a record to the application in one field or saving it to a change log.
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.
| number | text_value | array_value |
|---|---|---|
| 42 | Emma | ["sql","postgres"] |
The result table shows the JSON string without quotes; in psql it would be "Emma".
| employee |
|---|
| {"name":"Emma","bonus":300,"phone":"+44 7700 900123","salary":1200,"department":"backend"} |
| {"name":"Liam","bonus":null,"phone":null,"salary":1000,"department":"backend"} |
| {"name":"Olivia","bonus":150,"phone":"+44 7700 900456","salary":1350,"department":"frontend"} |
| {"name":"Noah","bonus":null,"phone":"+44 7700 900789","salary":950,"department":"frontend"} |
| {"name":"Sophia","bonus":200,"phone":null,"salary":1200,"department":"design"} |
Here e isn't a column but the whole row of the employees table.
| name | doc |
|---|---|
| Emma | {"name":"Emma"} |
| Liam | {"name":"Liam"} |
| Olivia | {"name":"Olivia"} |
| Noah | {"name":"Noah"} |
| Sophia | {"name":"Sophia"} |
JSONB_SET expects JSON, so we first convert the name from the column with TO_JSONB.
A text literal without a type won't work: TO_JSONB('Emma') throws could not determine polymorphic type because input has type unknown. Give it a type: TO_JSONB('Emma'::text).
TO_JSONB(NULL) is an SQL NULL, not a JSON null. If you need null itself, write 'null'::jsonb.
Dates become strings in ISO format: TO_JSONB(DATE '2026-09-13') gives "2026-09-13", and a timestamp gets a T in the middle: "2026-09-13T15:47:00".