JSON and JSONB both store JSON data. JSONB is generally preferred because, while it is slower to load, it is faster to query. The "B" in JSONB stands for binary and is sometimes referred to as "BSON". JSONB will remove trailing white spaces and any duplicate keys. This shouldn't be a problem for well-constructed JSON, but it's important to know. Read the [docs](https://www.postgresql.org/docs/current/datatype-json.html) before using this data type to understand it's limitations.
To insert JSON data, wrap the dictionary in single quotes and use double quotes for any string-like keys or values.
```SQL
CREATE TABLE my_json(
id SERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO my_json (data)
VALUES ('{"name": "Coffee Value Chains", "year": 2022, "results": {"indicator1": 100, "indicator2": 150}, "contacts":["Sergio", "Monica"]}'});
```
### Basic queries
Use the `->` operator to query within a JSON object.
```SQL
-- Select a property
SELECT data -> 'name' as name FROM my_json;
-- Select a property based on a condition
SELECT data -> 'results' FROM my_json WHERE data -> 'name' = '"Coffee Value Chains"';
```
You can chain multiple dash-arrow ( `->` ) operators to query within nested JSON. Alternatively, use the hash-arrow ( `#>` ) operator to query by passing a path of properties (wrapped in curly braces `{}` ). Queries for JSON and JSONB are the same.
```SQL
-- Select nested JSON object field by property
SELECT data -> 'results' -> 'indicator1' as indicator1 FROM my_json;
-- Alternative to select nested JSON object field by property
SELECT data #> '{results, indicator1}' as indicator1 FROM my_json;
```
The previous queries all return JSON objects so that you can continue to query them. The dash-double-arrow ( `->>` ) operator will return text from the query instead of JSON.
```SQL
-- Select a JSON object field as text
SELECT data->>'name' as name FROM users; -- you cannot chain after returning text
```
If your JSON contains arrays, you can index into the array using the query techniques described above combined with the element's index to get a specific element from an array.
```SQL
-- Select a JSON array element
SELECT data -> 'contacts'-> 0 as first_contact FROM my_json;
-- Alternative to select a JSON array element
SELECT data #> '{contacts, 0}' FROM my_json;
```
You can also unpack an array using the `jsonb_array_elements_text()` function.
```SQL
SELECT id, jsonb_array_elements_text(data -> 'contacts') FROM my_json;
```
### Contains queries
Use the contains operator ( `@>` ) to return records that include a specific property or property value.
```SQL
-- Select all records that contain a specific property
SELECT * FROM my_json WHERE data @> '{"results": "indicator1"}';
-- Select all records that contain a specific property value
SELECT * FROM my_json WHERE data @> '{"results": "indicator1: 100"}';
-- Select all records that contain a specific value(s) in an array
SELECT * FROM my_json WHERE data -> 'contacts' @> '["Monica"]';
```
Use the property-test ( `?` ) operator to query records based on the presence of one or more top-level properties.
```SQL
-- Select all records that contain a specific key
SELECT * FROM my_json WHERE data ? 'results';
-- Select all records that contain any of these keys
SELECT * FROM my_json WHERE data ?| ['results', 'contacts'];
-- Select all records that contain all of these keys
SELECT * FROM my_json WHERE data ?& ['results', 'contacts'];
```
### Updating JSON objects
Use the `jsonb_set()` function to update JSONB objects.
```SQL
UPDATE my_json SET data = jsonb_set(data, '{year}', 2023);
```
If any records do not have the property you want to update, you must first create it. To avoid overwriting those records that do have the property, use the `COALESCE` command with an empty object ( `'{}'` ).
```SQL
UPDATE my_json SET data = jsonb_set(data, '{"targets"}', COALESCE(data->'targets', '{}'));
SELECT jsonb_set(data, '{targets, indictaor1}', (SELECT data->'indicator1' FROM my_json j1 WHERE j1.id=j2.id)) FROM my_json j2;
```
### Indexing JSON objects
JSONB supports GIN indexes by means of `json_ops` and `jsonb_path_ops`. `json_ops` creates an index item for every property and value in the JSON whereas `json_path_ops` is more compact and faster to process but supports fewer operations.
> [!Tip]- Additional Resources
> - [DevHints.io/postgresql-json](https://devhints.io/postgresql-json)
> - [PostgreSQL JSON Functions & Operators](https://www.postgresql.org/docs/current/functions-json.html)
> - [PostgreSQL Tutorial JSON](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/)