Query JSON data

Oracle

-- Extract a scalar value with JSON_VALUE:
SELECT JSON_VALUE(data, '$.customer.city') AS city FROM orders;
-- Expand into rows with JSON_TABLE:
SELECT t.product, t.quantity
FROM orders s,
     JSON_TABLE(s.data, '$.items[*]'
       COLUMNS (product VARCHAR2(50) PATH '$.product',
                quantity NUMBER PATH '$.quantity')) t;

Oracle (12c+) queries JSON data with SQL/JSON path expressions. JSON_VALUE returns a single scalar value, JSON_QUERY an object/array fragment, and JSON_TABLE converts JSON into a relational row-column structure. 21c+ supports a native JSON data type.

PostgreSQL

-- jsonb operators:
SELECT data->'customer'->>'city' AS city FROM orders;
-- Containment (@>) and a GIN index:
CREATE INDEX ix ON orders USING GIN (data);
SELECT * FROM orders
WHERE data @> '{"status": "approved"}';

The PostgreSQL jsonb type offers rich operators: -> (json object/element), ->> (text value), #> (path), @> (containment), ? (key exists). A GIN index speeds up @> and ? queries. SQL/JSON path queries are also possible with jsonb_path_query.

MySQL

-- JSON_EXTRACT or shorthand operators:
SELECT JSON_EXTRACT(data, '$.customer.city') AS city FROM orders;
-- ->> returns unquoted text:
SELECT data->>'$.customer.city' AS city FROM orders
WHERE data->>'$.status' = 'approved';

MySQL (5.7+) queries the native JSON type with JSON_EXTRACT(); the -> operator is shorthand for JSON_EXTRACT, and ->> for JSON_UNQUOTE(JSON_EXTRACT()). Searches on JSON fields can be accelerated with a generated column + index.

SQL Server

-- Extract a scalar value with JSON_VALUE:
SELECT JSON_VALUE(data, '$.customer.city') AS city FROM orders;
-- Expand into rows with OPENJSON:
SELECT k.product, k.quantity
FROM orders s
CROSS APPLY OPENJSON(s.data, '$.items')
  WITH (product NVARCHAR(50) '$.product', quantity INT '$.quantity') k;

SQL Server (2016+) stores JSON in an NVARCHAR column. JSON_VALUE returns a scalar value, JSON_QUERY an object/array, and OPENJSON converts JSON into tabular form. ISJSON() validates it. Lookups on JSON paths can be accelerated with a computed column + index.

MongoDB

// MongoDB is natively a document (BSON/JSON) database.
// Access nested fields with dot notation:
db.orders.find({ "customer.city": "Ankara" })
// Array element and projection:
db.orders.find(
  { "items.product": "pen" },
  { "items.$": 1 }
)

MongoDB stores all data as documents (JSON-like BSON); querying it is native. Dot notation reaches nested fields and array elements. Complex transformations are done with the Aggregation Pipeline, and indexes give fast lookups on nested fields.

Redis

# Requires the RedisJSON module (Redis Stack).
# Write a JSON document:
JSON.SET order:1 $ '{"customer":{"city":"Ankara"},"status":"approved"}'
# Read a value with JSONPath:
JSON.GET order:1 $.customer.city

Core Redis has no JSON type; the RedisJSON module (Redis Stack) provides JSONPath-based reads and writes with JSON.SET/JSON.GET. Together with RediSearch, JSON documents can be indexed and queried. Without the module, JSON is stored as a string and parsed in the application.

Elasticsearch

# Documents are already JSON; query fields directly:
GET orders/_search
{ "query": { "term": { "customer.city": "Ankara" } } }
# For nested arrays, use the nested type and a nested query:
GET orders/_search
{ "query": { "nested": { "path": "items",
  "query": { "match": { "items.product": "pen" } } } } }

Elasticsearch stores and indexes documents natively as JSON. To preserve element boundaries in arrays of nested objects, use the 'nested' field type and a nested query; otherwise arrays of object type are flattened (risk of cross-matching). The object vs. nested distinction is critical for correctness.