JSON Types

8.14. JSON Types

8.14. JSON Types # 8.14.1. JSON Input and Output Syntax 8.14.2. Designing JSON Documents 8.14.3. jsonb Containment and Existence 8.14.4. jsonb …

https://www.postgresql.org/docs/current/datatype-json.html

Types

json

• Stores as text

• Each time an edit is made it must be re-parsed

• Preserves the original text, meaning whitespace and element ordering remains

jsonb

• Stored as binary

• Slightly slower to store

• Can be indexed and managed much more efficiently

• Does not preserve original text, meaning whitespace and element ordering is ignored

Syntax

Basic

-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::jsonb;

-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::jsonb;

-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::jsonb;

-- Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::jsonb;

Create Column

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    data JSONB
);

Insert

INSERT INTO users (data)
VALUES ('{"name": "Ben", "age": 30}');

Select

-- Get whole column
SELECT data FROM users;

-- Get field (text)
SELECT data->>'name' FROM users;

-- Get field (JSON)
SELECT data->'name' FROM users;

-- Nested
SELECT data->'address'->>'city' FROM users;

Update

Update an existing key

UPDATE users
SET data = jsonb_set(data, '{address,city}', '"London"')
WHERE id = 1;

Update add a new key value pair

UPDATE users
SET data = data || '{"email": "ben@example.com"}'
WHERE id = 1;

Update add 1

UPDATE users
SET data = jsonb_set(
    data,
    '{age}',
    ((data->>'age')::int + 1)::text::jsonb
)
WHERE id = 1;

Delete inside json

UPDATE users
SET data = data #- '{address,city}'
WHERE id = 1;