Common PostgreSQL JSON and JSONB query examples, including field access, nested objects, arrays, containment, updates, aggregation, indexing, and JSON construction.

sql-postgres-json-query-examples.sql

-- PostgreSQL JSON / JSONB Examples
--
-- PostgreSQL supports both JSON and JSONB.
--
-- JSON
--   Stores the original JSON text.
--
-- JSONB
--   Stores a binary representation that is faster to query and index.
--
-- In most applications, JSONB is the preferred choice.
--
-- Documentation:
-- https://www.postgresql.org/docs/current/functions-json.html


/* --------------------------------------------------------------------------
   Example table
--------------------------------------------------------------------------- */

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    profile JSONB NOT NULL
);


/* --------------------------------------------------------------------------
   Sample data
--------------------------------------------------------------------------- */

INSERT INTO users (profile)
VALUES
(
'{
    "name":"Alice",
    "age":31,
    "country":"Canada",
    "preferences":{
        "theme":"dark",
        "language":"en"
    },
    "tags":["developer","postgres"],
    "verified":true
}'
);


/* --------------------------------------------------------------------------
   1. Return the entire JSON document
--------------------------------------------------------------------------- */

SELECT profile
FROM users;


/* --------------------------------------------------------------------------
   2. Read a top-level field
--------------------------------------------------------------------------- */

SELECT
    profile -> 'country'
FROM users;


/* --------------------------------------------------------------------------
   3. Read a field as text
--------------------------------------------------------------------------- */

SELECT
    profile ->> 'country'
FROM users;


/* --------------------------------------------------------------------------
   4. Read nested values
--------------------------------------------------------------------------- */

SELECT
    profile
        -> 'preferences'
        ->> 'theme'
FROM users;


/* --------------------------------------------------------------------------
   5. Read nested path
--------------------------------------------------------------------------- */

SELECT
    profile #>> '{preferences,language}'
FROM users;


/* --------------------------------------------------------------------------
   6. Filter by JSON value
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE profile ->> 'country' = 'Canada';


/* --------------------------------------------------------------------------
   7. Numeric comparison
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE
(
    profile ->> 'age'
)::integer >= 30;


/* --------------------------------------------------------------------------
   8. Boolean comparison
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE
(
    profile ->> 'verified'
)::boolean;


/* --------------------------------------------------------------------------
   9. Check whether a key exists
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE profile ? 'country';


/* --------------------------------------------------------------------------
   10. Check multiple keys
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE profile ?& ARRAY[
    'country',
    'age'
];


/* --------------------------------------------------------------------------
   11. Check any key
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE profile ?| ARRAY[
    'country',
    'city'
];


/* --------------------------------------------------------------------------
   12. JSON contains object
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE profile @>
'{
    "country":"Canada"
}';


/* --------------------------------------------------------------------------
   13. JSON contains nested object
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE profile @>
'{
    "preferences":{
        "theme":"dark"
    }
}';


/* --------------------------------------------------------------------------
   14. JSON array contains value
--------------------------------------------------------------------------- */

SELECT *
FROM users
WHERE profile -> 'tags'
    @> '["postgres"]';


/* --------------------------------------------------------------------------
   15. Expand array elements
--------------------------------------------------------------------------- */

SELECT
    id,
    jsonb_array_elements_text(
        profile -> 'tags'
    ) AS tag
FROM users;


/* --------------------------------------------------------------------------
   16. Update one JSON value
--------------------------------------------------------------------------- */

UPDATE users
SET profile =
jsonb_set(
    profile,
    '{country}',
    '"United States"'
)
WHERE id = 1;


/* --------------------------------------------------------------------------
   17. Add a new key
--------------------------------------------------------------------------- */

UPDATE users
SET profile =
jsonb_set(
    profile,
    '{timezone}',
    '"UTC"',
    true
);


/* --------------------------------------------------------------------------
   18. Remove a key
--------------------------------------------------------------------------- */

UPDATE users
SET profile =
profile - 'verified';


/* --------------------------------------------------------------------------
   19. Remove nested key
--------------------------------------------------------------------------- */

UPDATE users
SET profile =
profile #- '{preferences,language}';


/* --------------------------------------------------------------------------
   20. Merge JSON objects
--------------------------------------------------------------------------- */

UPDATE users
SET profile =
profile ||
'{
    "active":true,
    "plan":"pro"
}';


/* --------------------------------------------------------------------------
   21. Build JSON
--------------------------------------------------------------------------- */

SELECT json_build_object(
    'id', id,
    'country',
    profile ->> 'country'
)
FROM users;


/* --------------------------------------------------------------------------
   22. Build JSONB
--------------------------------------------------------------------------- */

SELECT jsonb_build_object(
    'user_id', id,
    'profile', profile
)
FROM users;


/* --------------------------------------------------------------------------
   23. Aggregate rows into JSON
--------------------------------------------------------------------------- */

SELECT json_agg(profile)
FROM users;


/* --------------------------------------------------------------------------
   24. Aggregate JSON objects
--------------------------------------------------------------------------- */

SELECT jsonb_object_agg(
    id,
    profile
)
FROM users;


/* --------------------------------------------------------------------------
   25. Index JSONB
--------------------------------------------------------------------------- */

CREATE INDEX idx_users_profile
ON users
USING GIN (profile);

-- Supports many containment queries:

SELECT *
FROM users
WHERE profile @>
'{
    "country":"Canada"
}';


/* --------------------------------------------------------------------------
   26. Expression index
--------------------------------------------------------------------------- */

CREATE INDEX idx_users_country
ON users
(
    (
        profile ->> 'country'
    )
);

-- Supports:

SELECT *
FROM users
WHERE profile ->> 'country' = 'Canada';


/* --------------------------------------------------------------------------
   27. Useful notes
--------------------------------------------------------------------------- */

-- ->
--   Returns JSON / JSONB.

-- ->>
--   Returns text.

-- #>
--   Returns nested JSON.

-- #>>
--   Returns nested text.

-- ?
--   Key exists.

-- ?&
--   All keys exist.

-- ?|
--   Any key exists.

-- @>
--   JSON contains another JSON document.

-- ||
--   Merge JSON objects.

-- jsonb_set()
--   Update or create a value.

-- JSONB generally performs better than JSON because it supports indexing
-- and avoids reparsing the document on every query.

-- GIN indexes are typically the best choice for containment queries on JSONB.

-- If you frequently filter on one JSON field, consider an expression index
-- instead of relying solely on a general-purpose GIN index.