114 lines
3.1 KiB
Markdown
114 lines
3.1 KiB
Markdown
---
|
|
title: PostgreSQL JSON
|
|
prism_languages: [sql]
|
|
updated: 2018-12-06
|
|
category: Databases
|
|
---
|
|
|
|
## Operators
|
|
|
|
### Accessors
|
|
|
|
```sql
|
|
SELECT * FROM users WHERE data->>'name' = 'John';
|
|
SELECT data->>'name' AS name FROM users;
|
|
```
|
|
{: .-setup}
|
|
|
|
| Operator | Description | Example | Returns |
|
|
| ---- | ---- | ---- | ---- |
|
|
| `->` _int_ | Get array element `2` | `data->2` | JSON |
|
|
| `->` _text_ | Get object key `name` | `data->'name'` | JSON |
|
|
| `#>` _text[]_ | Get keypath `a,b` (eg, `data.a.b`) | `data#>'{a,b}'` | JSON |
|
|
| -
|
|
| `->>` _int_ | Get array element `2` | `data->>2` | Text |
|
|
| `->>` _text_ | Get object key `name` | `data->>'name'` | Text |
|
|
| `#>>` _text[]_ | Get keypath `a,b` (eg, `data.a.b`) | `data#>>'{a,b}'` | Text |
|
|
{: .-headers.-shortcuts}
|
|
|
|
`>` returns JSON, `>>` returns text.
|
|
|
|
### Boolean operators
|
|
|
|
```sql
|
|
SELECT * FROM users WHERE data->tags ? 'admin';
|
|
SELECT data->tags ? 'admin' AS is_admin FROM users;
|
|
```
|
|
{: .-setup}
|
|
|
|
| Operator | Description | Example |
|
|
| ---- | ---- | ---- |
|
|
| `?` _str_ | Does `data` have key `name`? | `data ? 'name'` |
|
|
| `?|` _text[]_ | Does `data` have `a` or `b`? | `data ?| array['a','b']` |
|
|
| `?&` _text[]_ | Does `data` have `a` and `b`? | `data ?& array['a','b']` |
|
|
| `@>` _jsonb_ | Does `left` include `right`? | `data @> '{"b":2}'::jsonb` |
|
|
| `<@` _jsonb_ | Does `right` include `left`? | `data <@ '{"a":1,"b":2}'::jsonb` |
|
|
{: .-headers.-shortcuts.-left-align}
|
|
|
|
When `?`/`?|`/`?&` works on objects, it checks keys; when it works on arrays, it checks for elements.
|
|
|
|
## Updating
|
|
|
|
### Arrays and objects
|
|
|
|
```sql
|
|
UPDATE users SET tags = tags || array['admin'];
|
|
```
|
|
{: .-setup}
|
|
|
|
| Operator | Example | Description
|
|
| ---- | ---- | ----
|
|
| `||` _json_ | `data || array['a','b']` | Concatenate
|
|
| `-` _str_ | `data - 'a'` | Delete a key
|
|
| `-` _int_ | `data - 1` | Delete an array item
|
|
| `#-` _text[]_ | `data #- '{us,name}'` | Delete a path
|
|
{: .-headers.-shortcuts}
|
|
|
|
Only available in PostgreSQL 9.5+.
|
|
|
|
### jsonb_set
|
|
|
|
```sql
|
|
UPDATE users SET data = jsonb_set(data, '{name}', '"John"');
|
|
```
|
|
|
|
Only available in PostgreSQL 9.5+.
|
|
|
|
## Functions
|
|
|
|
#### fn(json) → json
|
|
|
|
```sql
|
|
jsonb_set(data, '{path}', value)
|
|
jsonb_strip_nulls(data)
|
|
```
|
|
|
|
#### fn(···) → json
|
|
|
|
```sql
|
|
to_json("Hello"::text)
|
|
array_to_json('{1,2}'::int[])
|
|
```
|
|
|
|
#### Iteration
|
|
|
|
```sql
|
|
SELECT * from json_each('{"a":1, "b":2}')
|
|
SELECT * from json_each_text('{"a":1, "b":2}')
|
|
-- key | value
|
|
```
|
|
|
|
This is an incomplete list, there's way too many!
|
|
|
|
See: [JSON functions](https://www.postgresql.org/docs/9.5/static/functions-json.html)
|
|
|
|
## More examples
|
|
|
|
- `'{"a":1}'::jsonb ? 'a'`
|
|
- `'["a"]'::jsonb ? 'a'`
|
|
|
|
## References
|
|
|
|
- <https://www.postgresql.org/docs/9.5/static/functions-json.html>
|
|
- <https://www.postgresql.org/docs/9.5/static/datatype-json.html>
|