JSON Database Queries: PostgreSQL JSONB, MySQL JSON, and SQLite

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 19, 2026

SQL databases offer native JSON column types — PostgreSQL's jsonb compiles JSON to binary for fast key lookup, while MySQL's JSON type validates on insert and exposes path operators. PostgreSQL jsonb operators: -> returns JSON, ->> returns text, @> tests containment, ? checks key existence. Index a jsonb path with CREATE INDEX idx_user_city ON users USING gin(profile jsonb_path_ops) — this reduces full-table scans to index lookups. This guide covers PostgreSQL jsonb CRUD, MySQL JSON functions, SQLite JSON1 extension, indexing JSON paths, aggregating JSON with json_agg, and Prisma JSON field queries.

PostgreSQL JSONB CRUD Operations

The jsonb column type is PostgreSQL's primary way to store and query semi-structured data. Unlike the plain json type, jsonb parses and stores JSON in a binary format on write, making reads and index lookups significantly faster.

-- CREATE table with jsonb column
CREATE TABLE users (
  id      SERIAL PRIMARY KEY,
  email   TEXT NOT NULL,
  profile JSONB NOT NULL DEFAULT '{}'
);

-- INSERT: pass JSON as a string literal; PostgreSQL validates and converts
INSERT INTO users (email, profile)
VALUES ('alice@example.com', '{"name":"Alice","city":"London","role":"admin"}');

-- SELECT: -> returns jsonb, ->> returns text
SELECT
  email,
  profile -> 'name'   AS name_json,   -- returns "Alice" (jsonb)
  profile ->> 'city'  AS city_text     -- returns London  (text, no quotes)
FROM users
WHERE profile ->> 'city' = 'London';

-- UPDATE a single key without rewriting the whole document
UPDATE users
SET profile = jsonb_set(profile, '{city}', '"Paris"')
WHERE email = 'alice@example.com';

-- UPDATE nested key: jsonb_set(col, '{parent,child}', value)
UPDATE users
SET profile = jsonb_set(profile, '{address,zip}', '"75001"', true)
WHERE id = 1;

-- DELETE a key from a jsonb object using the - operator
UPDATE users
SET profile = profile - 'role'
WHERE id = 1;

-- DELETE a nested path using #- operator
UPDATE users
SET profile = profile #- '{address,zip}'
WHERE id = 1;

jsonb_set(target, path, new_value, create_missing) is the standard function for targeted updates. Pass true as the fourth argument to create the key if it does not already exist. The - operator removes a top-level key; #- removes a nested path specified as a text array.

PostgreSQL JSONB Operators and Functions

Beyond -> and ->> , PostgreSQL provides a rich set of jsonb operators and functions for containment, existence, iteration, and construction. Choosing the right operator matters for both correctness and performance — only @>, ?, ?|, and ?& can use a GIN index.

-- @> containment: does the left jsonb contain the right jsonb?
SELECT * FROM users WHERE profile @> '{"role":"admin"}';

-- ? key existence: does the jsonb have this top-level key?
SELECT * FROM users WHERE profile ? 'email';

-- ?| any-key existence: does the jsonb have ANY of these keys?
SELECT * FROM users WHERE profile ?| ARRAY['phone', 'email'];

-- ?& all-key existence: does the jsonb have ALL of these keys?
SELECT * FROM users WHERE profile ?& ARRAY['name', 'city', 'role'];

-- #> nested path (returns jsonb): equivalent to chained ->
SELECT profile #> '{address,city}' FROM users;

-- #>> nested path (returns text)
SELECT profile #>> '{address,city}' FROM users;

-- jsonb_each: expand a jsonb object into (key text, value jsonb) rows
SELECT key, value FROM users, jsonb_each(profile) WHERE id = 1;

-- jsonb_array_elements: expand a jsonb array into rows
SELECT elem FROM orders, jsonb_array_elements(items) AS elem;

-- jsonb_build_object: construct a jsonb object from key/value pairs
SELECT jsonb_build_object('id', id, 'email', email) FROM users;

-- jsonb_object_keys: return the set of top-level keys
SELECT jsonb_object_keys(profile) FROM users WHERE id = 1;

-- || merge two jsonb objects (right-side wins on key conflict)
UPDATE users
SET profile = profile || '{"verified": true}'
WHERE id = 1;

The || merge operator is a convenient way to add or overwrite multiple keys in one statement without calling jsonb_set repeatedly. For merging deeply nested objects, use jsonb_set or a custom recursive merge function, since || only merges at the top level.

Indexing JSONB for Performance

Without an index, every jsonb query requires a sequential scan of the full column. A GIN index eliminates this for containment and existence queries, dropping complexity from O(n) to O(log n). Choose the operator class based on which operators you need.

-- GIN index with jsonb_path_ops: supports @> only, but smaller and faster
CREATE INDEX idx_users_profile_path ON users USING gin(profile jsonb_path_ops);

-- GIN index with default jsonb_ops: supports @>, ?, ?|, ?&
-- Larger index, but covers all containment and existence operators
CREATE INDEX idx_users_profile ON users USING gin(profile);

-- Expression index: used for equality queries on a specific extracted path
-- Useful when you always query the same key
CREATE INDEX idx_users_city ON users ((profile ->> 'city'));
-- This index is used by: WHERE profile ->> 'city' = 'London'

-- Expression index on a numeric path (cast required for range queries)
CREATE INDEX idx_users_age ON users ((( profile ->> 'age')::int));
-- Used by: WHERE (profile ->> 'age')::int > 30

-- Partial GIN index: only index active users' profiles
CREATE INDEX idx_active_users_profile
  ON users USING gin(profile)
  WHERE active = true;

-- Verify index use with EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"role":"admin"}';
-- Look for "Bitmap Index Scan on idx_users_profile_path" in the output

Prefer jsonb_path_ops when your queries only use @> — the index is 20–30% smaller than jsonb_ops and typically faster. Use jsonb_ops when you also need ?, ?|, or ?& operators. Expression indexes are the right choice when you filter on a specific extracted key with equality or range comparisons, as GIN indexes do not accelerate these patterns.

MySQL JSON Column Type

MySQL 5.7 introduced a native JSON column type that validates JSON on insert, stores it in an internal binary format, and provides a family of JSON_* functions. MySQL 5.7.9 added shorthand path operators (-> and ->>) that mirror PostgreSQL's syntax.

-- CREATE table with JSON column
CREATE TABLE products (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(255) NOT NULL,
  metadata JSON NOT NULL
);

-- INSERT: MySQL validates JSON syntax on insert
INSERT INTO products (name, metadata)
VALUES ('Widget', '{"price": 9.99, "tags": ["sale", "featured"], "stock": 100}');

-- SELECT with JSON_EXTRACT (returns JSON, including quotes on strings)
SELECT JSON_EXTRACT(metadata, '$.price') FROM products;   -- returns 9.99
SELECT JSON_EXTRACT(metadata, '$.tags[0]') FROM products; -- returns "sale"

-- Shorthand -> operator (equivalent to JSON_EXTRACT, returns JSON)
SELECT metadata -> '$.price' FROM products;

-- Shorthand ->> operator (equivalent to JSON_UNQUOTE(JSON_EXTRACT(...)), returns text)
SELECT metadata ->> '$.tags[0]' FROM products;  -- returns: sale (no quotes)

-- WHERE clause filtering
SELECT * FROM products WHERE JSON_EXTRACT(metadata, '$.stock') > 50;
SELECT * FROM products WHERE metadata ->> '$.status' = 'active';

-- JSON_CONTAINS: check if a value is contained (works for arrays and objects)
SELECT * FROM products WHERE JSON_CONTAINS(metadata -> '$.tags', '"featured"');

-- JSON_SET: update one or more paths
UPDATE products
SET metadata = JSON_SET(metadata, '$.price', 12.99, '$.stock', 80)
WHERE id = 1;

-- JSON_ARRAYAGG: aggregate values into a JSON array (MySQL 5.7.22+)
SELECT category, JSON_ARRAYAGG(name) AS product_names
FROM products GROUP BY category;

-- Generated column for indexed access on a JSON path
ALTER TABLE products
  ADD COLUMN price DECIMAL(10,2) GENERATED ALWAYS AS (metadata ->> '$.price') STORED,
  ADD INDEX idx_price (price);

MySQL does not support GIN indexes on JSON columns directly. The standard pattern is to add a generated column for any JSON path you need to query efficiently, then index that generated column. A stored generated column is recomputed on every write and physically stored, making it indexable; a virtual generated column is computed on read and cannot be indexed.

SQLite JSON1 Extension

SQLite's JSON1 extension provides a complete set of JSON functions. It is compiled in by default since SQLite 3.38.0. Store JSON in a TEXT column and use json_valid() to check well-formedness; SQLite has no native JSON column type with built-in validation.

-- Table using TEXT column for JSON storage
CREATE TABLE events (
  id      INTEGER PRIMARY KEY,
  payload TEXT NOT NULL CHECK (json_valid(payload))
);

-- INSERT with validation via CHECK constraint
INSERT INTO events (payload)
VALUES ('{"type":"click","user":{"id":42,"name":"Bob"},"ts":1716105600}');

-- json_extract: read a value from a path
SELECT json_extract(payload, '$.type') FROM events;           -- click
SELECT json_extract(payload, '$.user.name') FROM events;      -- Bob
SELECT json_extract(payload, '$.user.id') FROM events;        -- 42

-- json_object: build a JSON object from key/value pairs
SELECT json_object('id', id, 'type', json_extract(payload, '$.type')) FROM events;

-- json_set / json_insert / json_replace: modify a document
UPDATE events
SET payload = json_set(payload, '$.processed', 1)
WHERE id = 1;

-- json_each: table-valued function to iterate a JSON array
SELECT key, value
FROM events, json_each(json_extract(payload, '$.tags'))
WHERE id = 1;

-- json_group_array: aggregate column values into a JSON array
SELECT json_group_array(json_extract(payload, '$.type'))
FROM events;

-- json_group_object: aggregate key/value pairs into a JSON object
SELECT json_group_object(
  CAST(id AS TEXT),
  json_extract(payload, '$.type')
) FROM events;

-- Indexed access via generated column (SQLite 3.31+)
ALTER TABLE events ADD COLUMN event_type TEXT
  GENERATED ALWAYS AS (json_extract(payload, '$.type')) STORED;
CREATE INDEX idx_event_type ON events (event_type);

The CHECK (json_valid(payload)) constraint is the closest SQLite equivalent to a native JSON type — it rejects any insert or update where the value is not valid JSON. Because SQLite lacks GIN-style indexes, the generated column + regular index pattern is the only way to efficiently query specific JSON paths.

Aggregating JSON with json_agg and json_build_object

A common N+1 problem in applications is fetching a list of parent rows, then issuing one query per row to fetch related child rows. PostgreSQL's json_agg and json_build_object solve this by building the entire nested JSON response in a single SQL statement.

-- Basic json_agg: collect order IDs per user into a JSON array
SELECT
  user_id,
  json_agg(id ORDER BY created_at DESC) AS order_ids
FROM orders
GROUP BY user_id;

-- json_agg with json_build_object: collect full order objects per user
SELECT
  u.id,
  u.email,
  json_agg(
    json_build_object(
      'id',      o.id,
      'total',   o.total,
      'status',  o.status,
      'created', o.created_at
    ) ORDER BY o.created_at DESC
  ) FILTER (WHERE o.id IS NOT NULL) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

-- jsonb_agg and jsonb_build_object: same as above but returns jsonb
SELECT
  u.id,
  jsonb_agg(
    jsonb_build_object('id', o.id, 'total', o.total)
    ORDER BY o.created_at DESC
  ) FILTER (WHERE o.id IS NOT NULL) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

-- Nested aggregation: users with orders, each order with line items
SELECT
  u.id,
  u.email,
  json_agg(
    json_build_object(
      'order_id', o.id,
      'items', (
        SELECT json_agg(json_build_object('product', li.product_name, 'qty', li.quantity))
        FROM line_items li WHERE li.order_id = o.id
      )
    )
  ) FILTER (WHERE o.id IS NOT NULL) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

The FILTER (WHERE o.id IS NOT NULL) clause prevents a null entry from appearing in the array when a user has no matching orders (a common bug when using LEFT JOIN with json_agg). Without FILTER, users with no orders get an array containing a single null element instead of an empty array or null aggregate.

Prisma JSON Field Queries

Prisma exposes JSON columns as Prisma.JsonValue — a union of string | number | boolean | null | Prisma.JsonArray | Prisma.JsonObject. Prisma provides dedicated filter operators for JSON fields that map to the underlying database's JSON query capabilities.

// schema.prisma
model User {
  id      Int    @id @default(autoincrement())
  email   String @unique
  profile Json   @default("{}")
}

// --- TypeScript queries ---
import { PrismaClient, Prisma } from '@prisma/client'
const prisma = new PrismaClient()

// path + equals: find users where profile.city === 'London'
const londonUsers = await prisma.user.findMany({
  where: {
    profile: {
      path: ['city'],
      equals: 'London',
    },
  },
})

// string_contains: find users where profile.bio contains 'engineer'
const engineers = await prisma.user.findMany({
  where: {
    profile: {
      path: ['bio'],
      string_contains: 'engineer',
    },
  },
})

// array_contains: find users where profile.roles contains 'admin'
const admins = await prisma.user.findMany({
  where: {
    profile: {
      path: ['roles'],
      array_contains: ['admin'],
    },
  },
})

// Reading and typing a JSON field
const user = await prisma.user.findUniqueOrThrow({ where: { id: 1 } })
const profile = user.profile as Prisma.JsonObject
const city = profile['city'] as string          // typed access
const roles = profile['roles'] as string[]      // cast array

// Writing a JSON field: pass any Prisma.InputJsonValue
await prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      name: 'Alice',
      city: 'Paris',
      roles: ['admin', 'editor'],
    },
  },
})

// Merge update using Prisma.JsonNull for explicit null
await prisma.user.update({
  where: { id: 1 },
  data: { profile: Prisma.JsonNull },
})

Prisma's JSON filters (path, equals, string_contains, array_contains) are supported on PostgreSQL and MySQL. SQLite support is partial — simple equals works, but path-based filters may not be available depending on your Prisma version. For complex JSON queries not expressible through Prisma's filter API, use prisma.$queryRaw with a parameterized SQL string to call jsonb_path_query, JSON_EXTRACT, or any other native JSON function directly.

Key Terms

JSONB

PostgreSQL's binary JSON storage format. Unlike the plain json type which stores text verbatim, jsonb parses JSON into an internal binary representation on write, removing whitespace and deduplicating keys. The binary format enables GIN indexing and makes key lookups and operator evaluations significantly faster at read time. The trade-off is a slightly higher write cost due to parsing. All new PostgreSQL JSON columns should use jsonb unless there is a specific reason to preserve the original text.

GIN index

A Generalized Inverted Index — a PostgreSQL index type designed for composite values such as arrays, full-text search vectors, and jsonb documents. For jsonb, a GIN index stores entries for every key and value inside the JSON document, allowing the database to evaluate @> (containment) and ? (key existence) queries in O(log n) time instead of O(n). Two operator classes are available: jsonb_ops (supports @>, ?, ?|, ?&) and jsonb_path_ops (supports only @> but produces a smaller, faster index).

Path operator

A SQL operator or function that extracts a value from a JSON document by navigating a path expression. In PostgreSQL, -> extracts a JSON sub-value and ->> extracts a text value; #> and #>> accept a text-array path for nested access. In MySQL, path expressions use the $.field syntax used by JSON_EXTRACT() and the ->/->> shorthand. In SQLite, json_extract(col, '$.field') is the primary path function.

JSON containment

A test of whether one JSON value is a structural subset of another. In PostgreSQL, the @> operator returns true if the left jsonb value contains all keys and values present in the right jsonb value. For example, '{"role":"admin","city":"London"}' @> '{"role":"admin"}' is true. Arrays follow set semantics: '[1,2,3]' @> '[2,3]' is true. Containment checks can use a GIN index, making them efficient for filtering large tables by partial JSON document match.

Aggregation

The process of combining multiple rows into a single result value using aggregate functions. For JSON, PostgreSQL provides json_agg() and jsonb_agg() to collect row values into a JSON array, and json_build_object() / jsonb_build_object() to shape each row as a JSON object before aggregation. MySQL provides JSON_ARRAYAGG(). SQLite provides json_group_array() and json_group_object(). JSON aggregation in SQL eliminates application-side N+1 loops by building nested response structures in a single query.

Generated column

A column whose value is automatically computed from an expression rather than stored by the application. In the context of JSON queries, a generated column extracts a specific JSON path — e.g., GENERATED ALWAYS AS (metadata ->> '$.status') STORED in MySQL or GENERATED ALWAYS AS (json_extract(payload, '$.type')) STORED in SQLite — and stores the result in a regular column that can be indexed. This is the primary technique for adding index-backed access to JSON paths in databases that lack GIN-style JSON indexes.

JSON1 extension

SQLite's built-in library of JSON functions and table-valued functions, available since SQLite 3.9 and compiled in by default since 3.38.0. It provides json() for validation and normalization, json_extract() for path access, json_set()/json_insert()/json_replace()/json_remove() for modification, json_object()/json_array() for construction, and json_each()/json_tree() as table-valued functions for iteration. Check availability with SELECT json('{"ok":1}') — if it returns {"ok":1} the extension is active.

FAQ