postgresql-json.md

   1 ---
   2 title: PostgreSQL JSON
   3 layout: 2017/sheet
   4 prism_languages: [sql]
   5 updated: 2018-12-06
   6 category: Databases
   7 ---
   8
   9 ## Operators
  10
  11 ### Accessors
  12
  13 ```sql
  14 SELECT * FROM users WHERE data->>'name' = 'John';
  15 SELECT data->>'name' AS name FROM users;
  16 ```
  17 {: .-setup}
  18
  19 | Operator       | Description                        | Example          | Returns |
  20 | ----           | ----                               | ----             | ----    |
  21 | `->` _int_     | Get array element `2`              | `data->2`        | JSON    |
  22 | `->` _text_    | Get object key `name`              | `data->'name'`   | JSON    |
  23 | `#>` _text[]_  | Get keypath `a,b` (eg, `data.a.b`) | `data#>'{a,b}'`  | JSON    |
  24 | -
  25 | `->>` _int_    | Get array element `2`              | `data->>2`       | Text    |
  26 | `->>` _text_   | Get object key `name`              | `data->>'name'`  | Text    |
  27 | `#>>` _text[]_ | Get keypath `a,b` (eg, `data.a.b`) | `data#>>'{a,b}'` | Text    |
  28 {: .-headers.-shortcuts}
  29
  30 `>` returns JSON, `>>` returns text.
  31
  32 ### Boolean operators
  33
  34 ```sql
  35 SELECT * FROM users WHERE data->tags ? 'admin';
  36 SELECT data->tags ? 'admin' AS is_admin FROM users;
  37 ```
  38 {: .-setup}
  39
  40 | Operator      | Description                   | Example                          |
  41 | ----          | ----                          | ----                             |
  42 | `?` _str_     | Does `data` have key `name`?  | `data ? 'name'`                  |
  43 | `?|` _text[]_ | Does `data` have `a` or `b`?  | `data ?| array['a','b']`         |
  44 | `?&` _text[]_ | Does `data` have `a` and `b`? | `data ?& array['a','b']`         |
  45 | `@>` _jsonb_  | Does `left` include `right`?  | `data @> '{"b":2}'::jsonb`       |
  46 | `<@` _jsonb_  | Does `right` include `left`?  | `data <@ '{"a":1,"b":2}'::jsonb` |
  47 {: .-headers.-shortcuts.-left-align}
  48
  49 When `?`/`?|`/`?&` works on objects, it checks keys; when it works on arrays, it checks for elements.
  50
  51 ## Updating
  52
  53 ### Arrays and objects
  54
  55 ```sql
  56 UPDATE users SET tags = tags || array['admin'];
  57 ```
  58 {: .-setup}
  59
  60 | Operator       |  Example                   |  Description
  61 | ----           |  ----                      |  ----
  62 | `||` _json_    |  `data || array['a','b']`  |  Concatenate
  63 | `-` _str_      |  `data - 'a'`              |  Delete a key
  64 | `-` _int_      |  `data - 1`                |  Delete an array item
  65 | `#-` _text[]_  |  `data #- '{us,name}'`     |  Delete a path
  66 {: .-headers.-shortcuts}
  67
  68 Only available in PostgreSQL 9.5+.
  69
  70 ### jsonb_set
  71
  72 ```sql
  73 UPDATE users SET data = jsonb_set(data, '{name}', '"John"');
  74 ```
  75
  76 Only available in PostgreSQL 9.5+.
  77
  78 ## Functions
  79
  80 #### fn(json) → json
  81
  82 ```sql
  83 jsonb_set(data, '{path}', value)
  84 jsonb_strip_nulls(data)
  85 ```
  86
  87 #### fn(···) → json
  88
  89 ```sql
  90 to_json("Hello"::text)
  91 array_to_json('{1,2}'::int[])
  92 ```
  93
  94 #### Iteration
  95
  96 ```sql
  97 SELECT * from json_each('{"a":1, "b":2}')
  98 SELECT * from json_each_text('{"a":1, "b":2}')
  99 -- key | value
 100 ```
 101
 102 This is an incomplete list, there's way too many!
 103
 104 See: [JSON functions](https://www.postgresql.org/docs/9.5/static/functions-json.html)
 105
 106 ## More examples
 107
 108 - `'{"a":1}'::jsonb ? 'a'`
 109 - `'["a"]'::jsonb ? 'a'`
 110
 111 ## References
 112
 113 - <https://www.postgresql.org/docs/9.5/static/functions-json.html>
 114 - <https://www.postgresql.org/docs/9.5/static/datatype-json.html>